dateadd not calculating properly??

[k0r54]

Hi,

When i use this

between #01/07/2005# and #01/09/2005#

It all works ok and pull outs what i need.

But when i do

between #01/07/2005# and DateAdd("m",3,#01/07/2005#)

I have also tried it without the # and with 2 instead of 3.

No luck

Any ideas?

Thanks
k0r54

 

[bat1799]

not tested, but the way SQL handles dates you may need
between #01/07/2005# and DateAdd("m",3,07/01/2005)

How are you going to get the dates in to the query? linked from a form or is this a VBA sql?

Peter

 

[k0r54]

Thankyou, but it does not work still . My date in the table is mm/dd/yyyy so i changed them both around and still nothing.

It is in a query and i will be gettin it to popup for the dates i.e [Date:]

Thanks
k0r54

 

[MStef]

Try this:
...........DateAdd("m";3;#01/07/2005#)
; instead ,

 

[ColinEssex]

My date in the table is mm/dd/yyyy

A date in a table is stored as a number. The format you see is the setup of Windows.
It seems your dates are set as US dates. If you are in the UK you may need to change this.

Col

 

[k0r54]

It is set to UK i have put them in there as mm/dd/yyyy, still no luck.

; says invalid syntax

It works fine though when i put in the two dates just doesn't when im doing dateadd :s

All your help is appreciated

Thanks
k0r54

 

[raskew]

Hi -

Tried it against Northwind's Orders table and it worked fine. Don't think it's the statement that's the problem.

My date in the table is mm/dd/yyyy ...

Open your table in design view and check the data type of your date field. It should be date/time. If, instead, it's text, there's the problem.

See the following for an explanation of how dates/times are stored by Access: http://support.microsoft.com/kb/q130514/

Bob

 

[Rich]

Are you sure your date field is defined as a date/time field and not as Text.
Also you should not have a field named as Date, in Access it's a reserved word

 

[bat1799]

Between [Start Date] And DateAdd("m",3,[Start Date])
Works for me

peter

 

[k0r54]

Ok, I have checked everything and still no luck.

I have simplified my query by JUST have the date field and still nothing. I THINK what the problem is.... I have my machine and access set to UK i have input the field as mm/dd/yyyy (American). If i was to use the format([start date],"mm/dd/yyyy") how would i use it within my query.

I can then try that.

All you help is great
k0r54

 

[k0r54]

Ok, it deffinatly is that.

Is it possible for it to be in the table as american

 

[k0r54]

Ok, thank you for all your help.

Have just changed the format to american and all is well

Thanks for every
Adam