Solved Parameter Query

[tonsos]

Hi, I have set up what I think is a simple parameter query....but for the life of me I don't now why it doesn't work. When I run the following select query

I get

All well and good.
When I do the following, and then type in "2"

I get

I'm stumped. It is the same issue even if it is 3, 4 etc

 

[bob fitz]

can you post a copy of the db with a few fictitious records to illustrate the problem

 

[tonsos]

can you post a copy of the db with a few fictitious records to illustrate the problem

the query is: qryDoc_Review_Within_X_Months

 

[bob fitz]

Try:
<CInt([How many months?])

 

[Gasman]

Val works just as well?

SELECT tblDOC_NUM.*, tblDOC_TYPE.PRE_SCRIPT, tblDOC_TYPE.Description, DateDiff("m",Date(),([Date Reviewed]+[Review Period (Months)]*30)) AS [Difference in Date]
FROM tblDOC_TYPE INNER JOIN tblDOC_NUM ON tblDOC_TYPE.[Document Type] = tblDOC_NUM.[Document Type]
WHERE (((DateDiff("m",Date(),([Date Reviewed]+[Review Period (Months)]*30)))<Val([How many months?])) AND ((tblDOC_TYPE.Active)=True))
ORDER BY tblDOC_NUM.[Document Type], tblDOC_NUM.[Document Number], tblDOC_TYPE.PRE_SCRIPT;

I think it was comparing alphabetically as the input was likely take as a string.?

 

[tonsos]

Try:
<CInt([How many months?])

Thank you for that! It did the trick. But why?

 

[tonsos]

Val works just as well?

SELECT tblDOC_NUM.*, tblDOC_TYPE.PRE_SCRIPT, tblDOC_TYPE.Description, DateDiff("m",Date(),([Date Reviewed]+[Review Period (Months)]*30)) AS [Difference in Date]
FROM tblDOC_TYPE INNER JOIN tblDOC_NUM ON tblDOC_TYPE.[Document Type] = tblDOC_NUM.[Document Type]
WHERE (((DateDiff("m",Date(),([Date Reviewed]+[Review Period (Months)]*30)))<Val([How many months?])) AND ((tblDOC_TYPE.Active)=True))
ORDER BY tblDOC_NUM.[Document Type], tblDOC_NUM.[Document Number], tblDOC_TYPE.PRE_SCRIPT;

I think it was comparing alphabetically as the input was likely take as a string.?
View attachment 114802

Ahhhhh

 

[Pat Hartman]

[Date Reviewed]+[Review Period (Months)]*30

Assuming [Date Reviewed] is a date, you are adding some number of days, NOT months to it. If you want to add months, you MUST use dateAdd(). Because SomeDate + SomeInteger assumes the second addend is days.

Also, using embedded spaces and special characters in your names is really hard to read AND forces you to use [] constantly to enclose the badly formated names so that they stop offending VBA.