vba date sql question

[swarv]

Hi all,

I have the following code under a command button (OK):

Dim strcondition As Variant
On Error Resume Next
Dim countme As String
Dim team As String
countme = "countme"
team = "IT"
strcondition = "[Start_Date] BETWEEN #" & Me.txt_date & "# AND #" & Me.txtEndDate & "# AND absent.absentcode = '" & countme & "' AND tbl_users.team = '" & team & "'" 
DoCmd.OpenReport "absent2", acViewPreview, , strcondition

This seems to work fine
When it opens absent2 report it displays the wrong dates in the report.
If I pick a date that starts 01 it works but any other date and it'll display as shown.
Is there a reason for this?

Thanks
Martin

 

[pbaldy]

Perhaps:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210069

 

[raskew]

Paul -

Good call! Good reference! That's a keeper.

Bob

Oops! May be spoken too soon.

How to deal with these similar situations?

? makeusdate("16-Jan-09")
#1/16/2009#

? makeusdate("16/1/09")
#1/9/2016#

 

[swarv]

Hi both,

Thanks for the help.

I now have the following in a query:

SELECT MakeUSDate([Start_Date]) AS Expr1
FROM absent
ORDER BY MakeUSDate([Start_Date]);

And my absent table has a field called Start_Date. in there are dates such as 18/01/2009 and 03/02/2009 etc...

I have inserted this into a module called makeusdate:

Function MakeUSDate(x As Variant)
    If Not IsDate(x) Then Exit Function
    MakeUSDate = "#" & Day(x) & "/" & Month(x) & "/" & Year(x) & "#"
End Function

My query doesn't seem to return any results.

Also Where should I call this function for that the database report show correctly?

Many Thanks

Martin

 

[Kiwiman]

Howzit

Haven't looked at the function but you can try in your query:

Dim strUSDate

strUSDate = "mm/dd/yy"

strcondition = "[Start_Date] BETWEEN #" & format(Me.txt_date,strUSDate) & "# AND #" & format(Me.txtEndDate,strUSDate) & "# AND absent.absentcode = '" & countme & "' AND tbl_users.team = '" & team & "'" 
DoCmd.OpenReport "absent2", acViewPreview, , strcondition"

 

[swarv]

Hi,

Please find attached 2 images. 1 showing the report and form and the other showing the design of the report and the filter of the report.

I still haven't got this working.
When I choose 1 Feb 2009 it brings up the correct date but when I choose start_Date between 06/02/2009 and 20/02/2009 it brings up records which have start date in march and some even later than that.

Any ideas?

Thanks

Martin

 

[Kiwiman]

Howzit


Your USDate format should be "mm/dd/yy" so SQL can interpret it properly. You have it as "dd/mm/yy" - in jpg 1. Try it with "mm/dd/yy" and see if it returns the correct data.

 

[swarv]

Howzat.....

That seems to work. Cheers for that - I'll keep testing anyway just to make sure but at the moment it seems to work.

Thanks again

Martin