Date range issues (1 Viewer)

[Mcgrco]

Hi , I trying to create a sql query in code that queries a holiday table between two dates. the trouble is that the code seems to format the date in the us style meaning that for the 1st to the 6th of Jan I get everything up to the June. Can anyone help . the query results are below and . Any help appreciated. i tried using # but the same problem exists

SELECT [tbl ref Public Holidays].[Holiday Date], Weekday([Holiday Date]) AS Test
FROM [tbl ref Public Holidays]
WHERE ((([tbl ref Public Holidays].[Holiday Date]) Between "1/1/2005" And "6/1/2005"));

 

[namliam]

WHERE ((([tbl ref Public Holidays].[Holiday Date]) Between #1/1/2005# And #1/6/2005#));

Dates like these are US format (MM/DD/YYYY) If you want to make sure you never run into trouble, use Dateserial (YYYY,MM,DD) this never goes wrong....

Regards

 

[Mcgrco]

thanks but it just doesnt seem to workl for me. here is my code. This is so frustrating

Public Function CreateQueryForHolidays(dtStartdate As Date, dtMatdate As Date, strCurrency As String, strCountry As String)
Dim quniSql As String
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb
Const QUOTE As String = """"


dtStartdate = DateSerial(Mid(dtStartdate, 7, 4), Mid(dtStartdate, 4, 2), Mid(dtStartdate, 1, 2))
dtMatdate = DateSerial(Mid(dtMatdate, 7, 4), Mid(dtMatdate, 4, 2), Mid(dtMatdate, 1, 2))


quniSql = " "

quniSql = quniSql & " SELECT [tbl ref Public Holidays].[Holiday Date], [tbl ref Public Holidays].[Iso Country Code]"
quniSql = quniSql & " FROM [tbl ref Public Holidays]"
quniSql = quniSql & " WHERE ((([tbl ref Public Holidays].[Holiday Date]) Between " & dtStartdate & " And " & dtMatdate & "));"


Set qdf = db.QueryDefs("query1")
qdf.sql = quniSql
db.QueryDefs.Refresh


End Function

 

[Jon K]

The Mid() function works on text strings, not date values.

You can use the Format() function to format the dtStartdate and dtMatdate for Access.


Dim quniSql As String
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb
Const QUOTE As String = """"

quniSql = quniSql & " SELECT [tbl ref Public Holidays].[Holiday Date], [tbl ref Public Holidays].[Iso Country Code]"
quniSql = quniSql & " FROM [tbl ref Public Holidays]"
quniSql = quniSql & " WHERE [tbl ref Public Holidays].[Holiday Date] Between #" & Format(dtStartdate, "m/d/yyyy") & "# And #" & Format(dtMatdate, "m/d/yyyy") & "#;"

Set qdf = db.QueryDefs("query1")
qdf.SQL = quniSql
.

 

[namliam]

Public Function CreateQueryForHolidays(dtStartdate As Date, dtMatdate As Date, strCurrency As String, strCountry As String)

The fields are allready comming in as dates you can simply use them straigth away, they should be in the needed format

quniSql = quniSql & " WHERE ((([tbl ref Public Holidays].[Holiday Date]) Between #" & dtStartdate & "# And #" & dtMatdate & "#));"[code]
So without the dateserial in there.

If this does not return the proper format then the dates send into your function are wrong and you should maybe work with dateserial there rather than inside the function.

Also you should allways disambiguate:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Just to make sure.....

 

[Mcgrco]

Thanks for all your help. I solved the porblem by formating the etxt string. The codes looked like this Between #" & Format(CVDate(dtStartdate), "dd mmm yyyy") & "# And #" & Format(CVDate(dtMatdate), "dd mmm yyyy") & "#))