Hi, I have a follow up on a ques

[dmbfan715]

Hi,

I have a follow up on a question I posted a few weeks ago. What I'm trying to do is find all the records that are >= minus one month from a user entered date. My data goes starts at 1/1/02 and goes through 12/1/03. FYI, the field "BeginDate" always starts on the 1st of the month, hence the '1' in the Date Format. This query doesn't work for a January start date. I think I know why it's not working (1-1= month "0": not possible), but I can't think of what to do...Is there an easy way to fix this? Thanks in advance!

Shivani




SELECT tblEntitlement.EntitlementID, tblEntitlement.BeginDate, tblEntitlement.EndDate
FROM tblEntitlement
WHERE (((tblEntitlement.BeginDate)>=Format(DatePart("m",[Enter start date])-1 & "/" & "1" & "/" & DatePart("yyyy",[Enter start date]),"Short Date&quot));

 

[WildHare]

Shivani, I'm not clear on exactly what's going on in your code, but usually when I see a bunch of data stuff with "/" concatenations in it, I get suspicious that you might be going about this the wrong way.

If you are prompting a user for a start date and an end date, are they entering a complete date, or just a month? Or just a month/year?

There are many better ways to produce date boundary values than your use of the FORMAT and DatePart functions, and the ShortDate format itself.

For example, look at the DATEADD function, and/or the DateSerial function. See if they might handle your problem a little easier, and get back to us.

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[gol4]

did you try the dateadd function?

SELECT tblEntitlement.EntitlementID, tblEntitlement.BeginDate, tblEntitlement.EndDate
FROM tblEntitlement
WHERE (((tblEntitlement.BeginDate)>=dateadd("m",-1'[Enter start date])

and if its a matter of formating

WHERE (((tblEntitlement.BeginDate)>=format(dateadd("m",-1'[Enter start date]),"mm/dd/yyyy&quot

 

[Steve101]

Try the following:

I've simpliefied your select statement a little:

SELECT EntitlementID, BeginDate, EndDate
FROM tblEntitlement
WHERE BeginDate >= CVDate(format(dateadd("m",-1,[Enter start date]),"mm/01/yy&quot).

Hope this helps,


Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[dmbfan715]

Hi guys,

Thank you all so much for your help! I tried the methods y'all suggested and Steve's works very nicely. The user enters any date in the format ##/##/##. I use the "day" the user enters in other queries, but only the month is important in this one. The dateadd function had a syntax problem for some reason that I didn't know how to fix. This forum is awesome!

Shivani