Between problems

[@stan]

Help!
I have a report with an inbeded query.
I am running a between [start date] and [finish Date]
this worked fine last year but now it only wants to show dates for this year.
is there somthing that I am missing?

 

[namliam]

in what format are you entering your parameters? They need to be in US format, MM/DD/YYYY

 

[@stan]

it is stored as a long date but I am Using the "left([Date]),10) and it is in the mm/dd/yyyy

 

[namliam]

1) Date is a reserved word in access, you shouldnt use it as a column
2) you "cant" use left on a date field, use format if you are wanting it in a text format
3) a text of MM/DD/YYYY isnt workable for between purposes
4) Why format the date as text??? a date is date for a reason.
5) It isnt stored as LONG DATE, all dates are dates, period. It is FORMATTED as long date.
6) See you left an important part out of your OP, remove your left function and try again using US format for the parameters, I think you will find it magicaly works now.

 

[@stan]

Ok
while in the query how should it look?
the field name is 'shortdates" With out useing the Left, 10 how should I format it into US FORMAT?

thank you

 

[@stan]

Ok, iv droped the reformaing.
it is giving me records for only that fall inbetween the months and not the years.... so if I input 01/01/14 and 01/31/15 it gives the all records for the 1st month of 14 and 15.... If I input 2/2/2014 and 10/2/2014 it gives me all the dates between there for 14 and 15???

 

[namliam]

is because your date field isnt a date field and/or your parameters are not date fields.

As such your date is being treated as a string instead of a date... Make sure all 3 of the fields are date fields

 

[@stan]

they are text feilds

 

[namliam]

Yes well, with text fields it will break.... so change them to date fields.