Between dates query not working

[lilp32]

I cannot figure out what I am doing wrong. I am trying to write a query to put a "1" UNDER "POS_OTHER" when a culture (CULTUREDATE) is between [ADMIT_DATE] AND [DC_DATE]. All 3 date fields are formatted as vb general date/time, but there are many instances being missed (see screenshot).

SELECT Neg_on_admit_dates_formatted.REPID, Neg_on_admit_dates_formatted.ADMIT_DATE, Neg_on_admit_dates_formatted.DC_DATE, Neg_on_admit_dates_formatted.CULTUREDATE, IIf([CULTUREDATE] Between [ADMIT_DATE] And [DC_DATE],1,Null) AS POS_OTHER FROM Neg_on_admit_dates_formatted;

[IMGNOTHUMB]http://bytes.com/attachments/attachment/7584d1397229721/access_dates.jp g[/IMGNOTHUMB]

[Luuk]

seems OK

SELECT 
   Tabel1.Id, 
   Tabel1.ADMIT_DATE, 
   Tabel1.DC_DATE, 
   Tabel1.CULTUREDATE, 
   IIf([CULTUREDATE]>=[ADMIT_DATE] And [CULTUREDATE]<=[DC_DATE],1,Null) AS POS_OTHER, 
   IIf([CULTUREDATE] Between [ADMIT_DATE] And [CULTUREDATE],1,Null) AS POS_OTHER2
FROM Tabel1;

shows:

Id	ADMIT_DATE	DC_DATE	CULTUREDATE	POS_OTHER	POS_OTHER2
1	23-8-2005 14:02:00	1-11-2005 15:31:00	18-10-2005 20:45:01	1	1

(my date-format is DD-MM-YYYY, and time in 24-hours)

[lilp32]

Strange, but changing the format to 24-hours seems to have worked!

Thanks.

[Luuk]

When i change my dateformat to: 'mm/dd/yyyy hh:nn AM/PM'
It's still working:

Id	ADMIT_DATE	DC_DATE	CULTUREDATE	POS_OTHER	POS_OTHER2
1	08/23/2005 2:02 PM	11/01/2005 3:31 PM	10/18/2005 8:45 PM	1	1

;-)

[NeoPa]

I'm only guessing here, but from the name of the input source, the 'Dates' we're talking about are not really dates at all, but formatted date strings instead.

See Literal DateTimes and Their Delimiters (#) for some background that may help you understand what the difference is and why it's so important when working with dates in a database project.

I can confirm that, had the fields been actual date (Date/Time) fields then your original SQL would have worked perfectly without regard to the format of the values when shown.

NB. It always makes best sense to deal with the native date data when trying to work with dates. Formatted string values should only ever be used for interfacing with the human element. So, display formatted, but work in native (Date/Time).