Have I missed something with date formatting?

[Ruegen]

I have a form with a date field



the command button from anther form has criteria

DoCmd.OpenForm "frmBookingEditAdminStep", , , "BookingDate between#" & Format(Me.txtFrom, "dd\/mm\/yyyy") & "# and #" & Format(Me.txtUntil, "dd\/mm\/yyyy") & "# and StatusID<>5"

however I am getting dates 16/1/2013 instead of just dates 16/11/2013 - it's pulling dates in January - how do I avoid this??

[pbaldy]

Dates in VBA need to be in US format.

[Ruegen]

Dates in VBA need to be in US format.

I need the dates in Australian (dd/mm/yyyy)

when you say they need to be in us format - if I put 1/1/2013 until 1/12/2013 then I only get January as apposed to if I put 1/1/2013 until 30/12/2013 I get the whole year

[June7]

Review http://allenbrowne.com/ser-36.html

[Ruegen]

Aside from this madness (which cannot be turned off), just remember the user interface in Access uses the local Control Panel settings to interpret dates typed into the user interface.

sigh

[Ruegen]

I guess my next question is aside from making my regional settings setup in the windows control panel (already set) how should I handle dates?

If I have a field set in dd/mm/yyyy and access swaps it even though I have format(datefield,"dd/mm/yyyy) then how do I handle it so that it gives me the correct filter?

[pbaldy]

I need the dates in Australian (dd/mm/yyyy)

when you say they need to be in us format - if I put 1/1/2013 until 1/12/2013 then I only get January as apposed to if I put 1/1/2013 until 30/12/2013 I get the whole year

Apples and oranges. What if you use 1/1/2013 and 12/30/2013? Bottom line as I said, in VBA you have to use US format.

[John_G]

Hi -

Remember that date formatting is for display purposes (screen or print), or input mask only. These settings do not affect how MS Access stores dates.

Microsoft likes to assume that the whole world uses the illogical date format mm/dd/yyyy, and that's what you need to use in VBA. In your case, though, it is a simple fix. Change the statement in your original post from this:

DoCmd.OpenForm "frmBookingEditAdminStep", , , "BookingDate between#" & Format(Me.txtFrom, "dd\/mm\/yyyy") & "# and #" & Format(Me.txtUntil, "dd\/mm\/yyyy") & "# and StatusID<>5"

to this:

DoCmd.OpenForm "frmBookingEditAdminStep", , , "BookingDate between#" & Format(Me.txtFrom, "mm/dd/yyyy") & "# and #" & Format(Me.txtUntil, "mm/dd/yyyy") & "# and StatusID<>5"


All I did was change the format of the date in the text string - no changes in the data are needed.

HTH

John

[Ruegen]

Hi -

Remember that date formatting is for display purposes (screen or print), or input mask only. These settings do not affect how MS Access stores dates.

Microsoft likes to assume that the whole world uses the illogical date format mm/dd/yyyy, and that's what you need to use in VBA. In your case, though, it is a simple fix. Change the statement in your original post from this:

DoCmd.OpenForm "frmBookingEditAdminStep", , , "BookingDate between#" & Format(Me.txtFrom, "dd\/mm\/yyyy") & "# and #" & Format(Me.txtUntil, "dd\/mm\/yyyy") & "# and StatusID<>5"

to this:

DoCmd.OpenForm "frmBookingEditAdminStep", , , "BookingDate between#" & Format(Me.txtFrom, "mm/dd/yyyy") & "# and #" & Format(Me.txtUntil, "mm/dd/yyyy") & "# and StatusID<>5"


All I did was change the format of the date in the text string - no changes in the data are needed.

HTH

John

Thanks John,

I've got formatting on the text fields on forms as dd/mm/yyyy so I assume access will take dd/mm/yyyy and swap it to mm/dd/yyyy with the formatting and then process it correctly. I think this has fixed my problem.