If between two dates not working

[BarryMK]

I've got this is on the OnCurrrent Event of a form.

If I take out the "And Now() < #1/10/2006#" part it works, I've tried lots of code including varieties of "Between and" etc but I'm stuck.

If Now() > #6/30/2006# And Now() < #1/10/2006# Then
Me.Period = 1
Else: Me.Period = Null
End If

 

[Matt Greatorex]

Not sure why it's not working but how about:

If Now() > #6/30/2006# Then
If Now() < #1/10/2006# Then
Me.Period = 1
Else: Me.Period = Null
End If
End If

 

[Brianwarnock]

Am I reading this incorrectly you want it to be > 30th of June AND less than the 10th of January??

 

[workmad3]

depending on localisation settings, that could also be > 30th june and < 1 october the # symbols can cause a lot of trouble with localisation.... I find its better not to use them nowadays.

 

[Brianwarnock]

depending on localisation settings, that could also be > 30th june and < 1 october the # symbols can cause a lot of trouble with localisation.... I find its better not to use them nowadays.

I don't think you can have it both ways in the same statement

If Now() > #6/30/2006# And Now() < #1/10/2006#

Brian

 

[BarryMK]

Morning Guys Sorry I missed you yesterday pm but I'm back at the desk now. If only we had aircon! Thanks for the input.

The date range here is greater than 01 June and less than 01 Oct. I've tried between/and and can't get that working. I tried omitting the hache marks but that didn't work either. With the haches Access is messing around with the date formats. I type them in the Brit way EG 30/12/2006 and it transposes them as it sees fit. PC regional settings are for the UK.

Matt I'm afraid your solution doesn't work either. (I tried with and without the hache marks).

Ultimately I need to code a selection of date ranges between two dates (of which the example is one) so that a period marker is attached to records selected by a query. It shouldn't be this hard!

 

[allan57]

Try the following:

If Format(Now(), "yyyymmdd") > "20060601" And Format(Now(), "20061001") Then
Me.Period = 1
Else: Me.Period = Null
End If

 

[john471]

If Format(Now(), "yyyymmdd") > "20060601" And Format(Now(), "20061001")

??? I suspect allan57 Probably intended
If Format(Now(), "yyyymmdd") > "20060601" And Format(Now(), "yyyymmdd") < "20061001"

Which I expect would work...

...However - I think what you are saying about it "messing around with the date formats" is the key - the final code you posted demonstrates how it has "messed around".

You probably input #30/6/2006# - UK / AU etc format, (yes ?)

to which Access thinks to itself... "there aren't 30 months in a year, so he must've meant '#6/30/2006#' and changes it to that for you (what a help !!!)

Then when you input #1/10/2006# intending 01-OCT-2006, access says to itself "yep - 10-JAN-06, no problemo".

I prefer to input as #01-OCT-2006# or #30-JUN-2006#, and then I can be reasonably sure that accesss will interpret the date as I intended. I think it always uses US formats between #'s, and I think that is also what SQL demands. So alternately, you can just put in US formats yourself, but I always have to think twice about that and second-guess myself, so prefer to spell it out using a few extra keystrokes.

Also, if you wish to have no regard for the time component, it is adviseable to use Date() instead of Now().

HTH

Regards

John.

 

[BarryMK]

Allan57/John471 Thanks for the help. Allan57's code actually did the trick (with John's correction) hooray!

John I take your points re the #01-OCT-2006# date formatting and will bear it in mind, also you're right re Date() and Now(). The oddity here is the date field I'm using has to include time but I only need the date part for this little sub.

Thanks to all for great assistance as always.

 

[Brianwarnock]

I remain a touch confused. You state that the date range is greater than 1st June and less than 1st Oct but type 30/06/2006 ? Also I would remind you that time is always included in the comparison , it just defaults to 00:00:00 if you don't quote it. Thus Now()>30/06/2006 would include the 30/06/2006, but no doubt your final code allowed for this as John and Allan both used 01/06/2006.

Brian

 

[workmad3]

thats a good point if you want to do comparison with the current date, use Date() rather than Now(). Date() just gives you the date part.... makes more sense