Query/Report

[powerlock2005]

All,
I have a query (qry_license) that is tied to a table, and two fields I will be referring to are: dtLicenseBegin and results.

I have this expression for the results:

results: DateAdd("d",-60,[dtLicenseBegin])

So if I enter a date of 12/6/05 for the dtLicenseBegin, the results field will give me 60 days prior to 12/6/05.

I want to tie qry_license to a report, and I only want to show the records that are within the 60 days before the 12/6/05 date occurs.

Example: When 60 days prior to 12/6/05 occurs, which would be something like 10/6/05, I want the report to start showing that record until 12/6/05.

How do I accomplish this?

Please help someone.

Thanks,

 

[razchip]

Try results: Between DateAdd("d",-60,[dtLicenseBegin])AND DateAdd("d",-1,[dtLicenseBegin])

Thanks for the help.
Greg

 

[powerlock2005]

Greg,
I get a result of -1, and if I put anything other than ...("d",-1,[dtLicenseBegin]), I still get -1 as a result.

Meaning if I put -2, -3 , -4, etc., I still get -1 as a result.

I am simply trying to show when the 60 day period starts, I need to start showing the record in a report, but I'm not sure how to do this in a query.

Any other suggestions?

 

[dhookom]

Did you place Between DateAdd("d",-60,[dtLicenseBegin]) AND DateAdd("d",-1,[dtLicenseBegin])
in the criteria?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[powerlock2005]

Yes I did dhookom.

Instead of typing all of that, I just cut it short.

Sorry about the confusion.

So now what?

 

[dhookom]

How about sharing your entire SQL view?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[powerlock2005]

dhookom,
I am sorry it took me so long to get back to you, but here is the SQL view:

SELECT tblProducts.ProductID, tblProducts.FamilyID, tblProducts.ProductName, tblLicense.LicenseType, tblLicense.LicenseNumOps, tblInstallation.VersionNum, tblInstallation.System, tblInstallation.Install2, tblLicense.dtLicenseBegin, DateAdd("d",-60,[dtLicenseBegin]) AS results
FROM tblProducts INNER JOIN (tblLicense INNER JOIN tblInstallation ON tblLicense.LicenseID = tblInstallation.LicenseID) ON tblProducts.ProductID = tblInstallation.ProductID
ORDER BY tblProducts.ProductName;

I also get this error: "The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier", when I try to use:
results: Between DateAdd("d",-60,[dtLicenseBegin]) AND DateAdd("d",-1,[dtLicenseBegin])

Hope you can help me.

Thanks,

 

[dhookom]

Is it possible the dtLicenseBegin field might be null? If so, set the criteria to filter out the null values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]