Dates for report going CRAZY (Aus then USA)

[gangel]

ok so!!!

I have a macro that brings up a report based on the date of certain 'claims'.

="[ClaimDate] > #" & [StartDate] & "#"

HOWEVER it only works if the first number is bigger than 12, otherwise it thinks in AMERICAN!!



I use dd/mm/yyyy in my table which has a query that this is linked to.

however if i search for 1/5/14 it shows ALL claims after January (it uses the 1 as the month)
But if i use the same box on my form and type 13/5/14 it will find all claims after 13th MAY which is what i actually want!!!!

OMG i literally dont know why its trying to use USA dates!!!??

All my text boxes (for parameters) and tables and reports are set to SHORT DATE.

PLEASE point me in the right direction, im SOOOO FRUSTRATED

[pbaldy]

Try formatting it:

http://allenbrowne.com/ser-36.html#Format

[gangel]

Hey pbaldy,
Thanks for the response,

I found that page and i did read it but my coding is so bad I didnt really understand how to implement it.

Below is my macro converted to VBA using the access converter. I'm trying to do this so i can learn vba from what i know I made the macro with.
Pretty much i have 2 text boxes (StartDate and EndDate) and i want to know all the Claims in a report between those dates.

Im saying if there is no date, then show msg box, if no end date is used, then use today (using temp var as the end date parameter)

So where should i put the secnd lot of code to make this work correctly?

It is important to note that my system format is Australian (dd/mm/yyyy)

The tables are all working correctly as dd/mm/yyyy so its just a matter of getting the dates on the form correctly understood in the vba codes.

Function Macro1()On Error GoTo Macro1_Err


    With CodeContextObject
        If (IsNull(.StartDate)) Then
            Beep
            MsgBox "Please Enter Start Date", vbOKOnly, "No Date Entered"
            End
        End If
        TempVars.Add "TempEndDate", .EndDate
        If (IsNull(.EndDate)) Then
            TempVars.Add "TempEndDate", Now()
        End If
        DoCmd.OpenReport "TotalPaymentsMade", acViewReport, "", Eval("""[ClaimDate] Between #"" & [StartDate] & ""#"" & "" AND #"" & [TempVars]![TempEndDate] & ""#"""), acNormal
    End With




Macro1_Exit:
    Exit Function


Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit


End Function

Format([Dates],"mm/dd/yy")

[pbaldy]

You'd wrap [StartDate] and [TempVars]![TempEndDate] in the Format() function. Not sure why you need Eval() though, and all of the extra quotes.

[gangel]

hey again,
That is just what access did when it converted macro to VBA...

I dont even know how to run that function, do i have to add Sub or something??

[pbaldy]

I would expect it to be behind a button or some other event:

http://www.baldyweb.com/FirstVBA.htm