Solved VBA Query Date Criteria Using MM/DD/YYYY and not local DD/MM/YYYY (1 Viewer)

[LGDGlen]

Hi All

I am trying to query my data to get the top 1 record based on a todays date as part of the WHERE criteria but have found that if i use Date() no records are found when 1 should, if i put the date in manually it doesn't return records. This was getting very frustrating but then i just tried for the heck of it the date in MM/DD/YYYY format instead of the local UK format of DD/MM/YYYY and it works and returns the correct record.

Weird thing is if i create an ACTUAL query and run it directly it returns the right records so this seems to be an issue with doing it via VBA.

Todays date is 12th October 2021 - 12/10/2021

This returns 0 records when it should return 1
strConsDeliveriesSQL = "SELECT DISTINCT TOP 1 [FACT-Consignments].cons_id, [FACT-Deliveries].del_arrival_date,[FACT-Deliveries].del_id" & _
                       " FROM [FACT-Consignments] INNER JOIN [FACT-Deliveries] ON [FACT-Consignments].cons_id = [FACT-Deliveries].del_cons_id" & _
                       " WHERE [FACT-Consignments].cons_id = " & CStr(Me.cons_id) & " And [FACT-Deliveries].del_arrival_date >= #" & Cstr(Date) & "# ORDER BY [FACT-Deliveries].del_arrival_date DESC"

This returns 0 records when it should return 1
strConsDeliveriesSQL = "SELECT DISTINCT TOP 1 [FACT-Consignments].cons_id, [FACT-Deliveries].del_arrival_date,[FACT-Deliveries].del_id" & _
                       " FROM [FACT-Consignments] INNER JOIN [FACT-Deliveries] ON [FACT-Consignments].cons_id = [FACT-Deliveries].del_cons_id" & _
                       " WHERE [FACT-Consignments].cons_id = " & CStr(Me.cons_id) & " And [FACT-Deliveries].del_arrival_date >= #12/10/2021# ORDER BY [FACT-Deliveries].del_arrival_date DESC"

This works:
strConsDeliveriesSQL = "SELECT DISTINCT TOP 1 [FACT-Consignments].cons_id, [FACT-Deliveries].del_arrival_date,[FACT-Deliveries].del_id" & _
                       " FROM [FACT-Consignments] INNER JOIN [FACT-Deliveries] ON [FACT-Consignments].cons_id = [FACT-Deliveries].del_cons_id" & _
                       " WHERE [FACT-Consignments].cons_id = " & CStr(Me.cons_id) & " And [FACT-Deliveries].del_arrival_date >= #10/12/2021# ORDER BY [FACT-Deliveries].del_arrival_date DESC"

if i have to reformat the date to cope with this i will just wondering if there is a setting somewhere that i've missed, also i will need to run this query in UK and in Latin America so may need to cope with multiple date formats anyway

Thanks in advance

Glen

 

[Gasman]

Search here for jetdate, that is a constant I have offered before. On my phone in hospital car park, so easier for you. Use that format to get the correct one for dates.

 

[Gasman]

Found it https://www.access-programmers.co.u...uding-a-long-string-date.319438/#post-1783890

 

[LGDGlen]

brilliant, thank you i'll check the link and implement the suggestions and update this when its working

 

[LGDGlen]

@Gasman followed the link, read the thread and followed the link provided by @MajP of "Working smarter not harder" creating the Csql code to use. So thank you for pointing me in the right direction as i now have a module that will help me now and in the future, much appreciated

 

[Gasman]

@Gasman followed the link, read the thread and followed the link provided by @MajP of "Working smarter not harder" creating the Csql code to use. So thank you for pointing me in the right direction as i now have a module that will help me now and in the future, much appreciated

What?, you never used my constant? Well it wasn't mine really, I found it on the net, but it came in really handy.

Only kidding