VB6 Date using Dynamic SQL includes UNWANTED Time! 1

[mbu]

Hi everyone, this is my first question.

My VB6 form contains 2 ComboBox and they are filled using .Additem after the Data control is populated through a Dynamic SQL.

My Access2000 tabel (tblOpticsData) have Date field formated as Short Date for the Data Type Date/Time. The table also contains Time field formated as Short Time with Data Type Date/Time.

When I open the table in the Access the column contains ONLY dates (format is mm/dd/yyyy) but my Combobox got fill with dates and time (mm/dd/yyyy hh:mm:ss AM/PM)!

What is going on? The following is the code;

Data3SQL = "SELECT tblOpticsData.Date " & _
"FROM tblOpticsData " & _
"WHERE LensCut = '" & _
List1 & "' ORDER BY tblOpticsData.Date"
Data3.RecordSource = Data3SQL
Data3.Refresh
Data3.Recordset.MoveFirst
While Not Data3.Recordset.EOF
With Data3.Recordset
cboStartDate.AddItem ![Date]
cboEndDate.AddItem ![Date]
.MoveNext
End With
Wend

 

[johnwm]

Data will be stored as a full date/time usually.You can format the display as you wish. eg:
[tt]
cboStartDate.AddItem Format(![Date], "mm/dd/yyyy")
[/tt]
or any other Format string you prefer.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[mbu]

Thank You John!

Now I added a MonthView Control mvwStartDate (eventually there will be 2 controls) but I do not know the Syntax for using the mvwStartDate.Value in a Dynamic SQL

StartDate = mvwStartDate.Value
Data2SQL = "SELECT tblOpticsData.LensCut, tblOpticsData.Machine, tblOpticsData.LfX, tblOpticsData.LfY, tblOpticsData.RtX, tblOpticsData.RtY, tblOpticsData.Date " & _
"FROM tblOpticsData " & _
"WHERE LensCut = '" & List1 & "' AND [Date] BETWEEN # & StartDate & # AND Date ORDER BY tblOpticsData.Date"
Data2.RecordSource = Data2SQ

 

[johnwm]

I notice a couple of things here:
[tt]
Data2SQL = "SELECT tblOpticsData.LensCut, tblOpticsData.Machine, tblOpticsData.LfX, tblOpticsData.LfY, tblOpticsData.RtX, tblOpticsData.RtY, tblOpticsData.Date " & _
"FROM tblOpticsData " & _
"WHERE LensCut = '" & List1 & "' AND [Date] BETWEEN #" & StartDate & "# AND #" & Date & "# ORDER BY tblOpticsData.Date"
Data2.RecordSource = Data2SQL
[/tt]

If I have problems with a dynamic SQL string, I usually put in a [tt]
Debug.Print Data2SQL [/tt]
immediately before using it.

I guess the missing 'L' on the final line is a typo, or a missed cut&paste. If not, make sure you have Option Explicit set at the top of every module to ensure minimum typos in variable names. You can use Tools|Options|Require Variable declaration to automatically add the line to each module

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'