Between Parameter Query

[Dmcallister]

I have writen a query that figures out the age of an animal. Age: (Date()-[DOB])/7
this works fine. When I enter the criteria: Between [Please enter youngest age in weeks:] And [Please enter oldest age in weeks], I get wrong answers back. It appears that only ages over 10 weeks are returned. If I write the Between with actual numbers it works ok.

 

[pdx_man]

Try using the DateDiff function:

DateDiff('ww',Date(),[DOB])

 

[David R]

I thought that too, but I'm still getting the same errors that Dmcallister was. The only thing I can come up with is that the Type between the result of Datediff and the Criteria is different. Is there a way to typecast those Criteria?

By the way, DateDiff("w",...) will give the same calculated result as the original expression. "ww" adds the current week (I think).

[This message has been edited by David R (edited 12-11-2001).]

 

[David R]

And the syntax to get a positive result is Age: DateDiff("w",[DateofBirth],Date()).

Sorry I don't know the answer to why your query doesn't work. How good are you with VBA code?

 

[Dmcallister]

I have changed the Age function as you suggested, and as you have indicated the Between still does not work right. I do not know anything about VBA. I learn pretty quickly so if you wanted to give some hints I might be able to get it.

 

[pdx_man]

Force the parameter's data type to numeric:

Between [Please enter youngest age in weeks:]*1 And [Please enter oldest age in weeks]*1

 

[Dmcallister]

It worked wonderful. Thank you very much.

 

[nnej]

Thank you for posting this solution... it worked for me too. This board is great.

I don't really get why it works (don't " " imply text values?) but common sense tells me not to ask. So I'm going with it.

 

[sbenj69]

I'm pretty sure you could also have done it this way:

Between Val([Please enter youngest age in weeks:]) And Val([Please enter oldest age in weeks])

 

[raskew]

Hi -

It'd be helpful if you posted your SQL.

If you read the helpfile fine-print re DateDiff(), it's pretty obvious that
the MS Developers were having a particularly bad day when it came to intervals "d", "y", "w" & "ww".

If you're interested in full weeks: ? datediff("d", #7/1/08#, date())\7 will do it for you without confusion. Give it a try versus the "w" and "ww" intervals. At least you'll know what you're looking at.

If you've got a calculated field in your query, e.g.: Age: datediff("d", [DOB], date())\7

strSQL = "WHERE age between " & datediff("d", #7/1/08#, date())\7 & " AND " & datediff("d", #2/1/08#, date())\7 & ";"
debug.Print strSQL
where age between 3 AND 25;

Think that should work. Please let us know.

Bob