Try this:
SQL = "SELECT [cg1] FROM [tbl 3 LaborCG1] " & _
"WHERE [cg1] ='" & [txtCGID] & "' And [StartDate] BETWEEN #" & _
Format([txtStartDate], "yyyy-mm-dd") & "# AND #" & _
Format([txtEndDate], "yyyy-mm-dd") & "#"
This assumes cg1 is a text data type. If it’s a number data type omit
the single quotes.
The dates entered in the controls are first formatted in the ISO
standard date format of YYYY-MM-DD to make them internationally
unambiguous, and the resulting values are concatenated into the string
expression, with each wrapped in the # date delimiter characters.
When using a BETWEEN….AND operation you need to be aware of one
possible pitfall. If any of the values in the StartDate column have
non-zero times of day (there is no such thing in Access as a date
value per se, only date/time values) rows with any such date/time
values on the final day of the range will not be returned. Unless you
have specifically taken steps in the table definition to prevent dates
with non-zero times of day being entered in the column you cannot
assume that no such values are present, in which case the following is
a safer solution:
SQL = "SELECT [cg1] FROM [tbl 3 LaborCG1] " & _
"WHERE [cg1] ='" & [txtCGID] & "' And [StartDate] >= #" & _
Format([txtStartDate], "yyyy-mm-dd") & _
"# AND [StartDate] < #" & _
Format(DateAdd("d", 1, [txtEndDate]), "yyyy-mm-dd") & "#"
This restricts the results on dates on or before txtStartDate and
before the day following txtEndDate, so any row with values on
txtEndDate will be returned regardless of the time of day element in
the value.
Ken Sheridan
Stafford, England
Thanks for responding.
This gets 'Type Mismatch'.
SQL = "SELECT [tbl 3 LaborCG1].cg1 FROM [tbl 3 LaborCG1] WHERE [cg1] ='" &
[txtCGID] & "';" And "[StartDate] BETWEEN [txtStartDate] AND [txtEndDate]"
[StartDate] is in the query.
[txtStartDate] and [txtEndDate] are selected by the user to limit.
Can you give me a hint?
--
Chrissy
Dorian said:
"AND date-column-name BETWEEN [StartDate] AND [EndDate]"
Also, its best not to have any embedded spaces or any special chahcters in
your table names or column names.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
I test for presence of record for [CG1] and wish to add a date range. Dates
totally baffle me. (So does SQL)
How can I add [StartDate] and [EndDate] to the following SQL to test for
presence of record for [CG1] between [StartDate] and [EndDate]?
SQL = "SELECT [tbl 3 LaborCG1].cg1 FROM [tbl 3 LaborCG1] WHERE [cg1] ='" &
[txtCGID] & "';"
As always, I appreciate the help.
