Open Report from a Form

[FoxProProgrammer]

Hi folks,

I am designing a Form that allows users to select one of several reports, and output the report either to the printer or screen preview. The user can select the report and output destination via Option groups. The Form also has two text boxes where the user can enter a start date (startdate) and end date (enddate) for the report. These dates restrict the records that appear in the report. The Form has a Print button that opens the correct report and directs the output to the correct location. Here is the syntax for one of the reports:

DoCmd.OpenReport &quot;Production Run Summary&quot;, IIf(where.Value = 1, acViewPreview, acViewNormal), , (Date > Forms!Print!startdate.Value) And (Date <= Forms!Print!enddate.Value)

The Report, &quot;Production Run Summary&quot; is based on a Query, and one of it's columns (fields) is named &quot;Date&quot;. When I run the program, all the records are included in the report instead of the ones between the dates specified by startdate and enddate. Any ideas what I am doing wrong?

Thanks,
dz
dzaccess@yahoo.com

 

[VicM]

FoxProProgrammer:

Try putting the Date field inside square brackets [Date].

Hope this helps,

Vic

 

[FoxProProgrammer]

Thanks for the reply, Vic. I tried your suggestion, and get the following error:

&quot;...can't find the field 'Date' referred to in your expression.&quot;

I'm not sure why it can't find that field because it is in the report's record source. I opened the Report in Design View and clicked the three dots next to the Record Source to open the Query. The field named &quot;Date&quot; is in the output.

Any ideas what is causing this?

Thanks,
dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

Well, I solved this problem by setting the Record Source in the Open event of the Report instead of using the Where clause of the DoCmd.OpenReport Method. It works, but I am still curious if anyone knows why the Where clause doesn't work as expected.

Thanks a lot!

dz
dzaccess@yahoo.com

 

[Maquis]

Hi DZ, I don't know if this is your problem or not, but try changing the name of your field. &quot;Date&quot; is a reserved word in Access. It is a function name which returns today's date.

I ran into the same problem when I started programming in Access. I named several of my fields &quot;Date&quot; and then couldn't figure out why they weren't acting right. Changing the field name to something a bit more descriptive solved all my woes. Maq
<insert witty signature here>

 

[FoxProProgrammer]

Thanks for the reply, Maq. I definitely could have picked a better name for the field, but I'm not sure that's the problem. The syntax for one of the other reports is:

DoCmd.OpenReport &quot;Calibration Report&quot;, IIf(where.Value = 1, acViewPreview, acViewNormal), , &quot;([Cal Due]> Forms!Print!startdate.Value) And ([Cal Due] <= Forms!Print!enddate.Value)&quot;

When this executes, all the records show up in the report output, not just the ones between the two dates. This is the same problem that I had with the report that I described in the first post of this thread. If I could get past that issue, I could determine if the field name 'date' is causing a problem. At any rate, the program works with the workaround that I described, but I am curious why the Where clause in the DoCmd.OpenReport doesn't work as expected.

Thanks a lot. I will heed your advice when I name fields in the future as it avoids confusion even if it doesn't create a problem.
dz
dzaccess@yahoo.com

 

[Maquis]

Oh, I see the problem with that code. Sorry, I should have caught it earlier. You need to take your variable names outside the quotes, otherwise they get interpretted literally.

for example
X = &quot;Mary&quot;
Criteria = &quot;Where FirstName = X&quot; <---- Wrong!!!
Criteria = &quot;Where Firstname = '&quot; & X & &quot;'&quot; <---- Right!


Try this (warning: this is air code, the syntax may not be exactly right)

Criteria = &quot;[Cal Due] between #&quot; & Forms!Print!startdate.Value & &quot;# And #&quot; & Forms!Print!enddate.Value & &quot;#&quot;
DoCmd.OpenReport &quot;Calibration Report&quot;, IIf(where.Value = 1, acViewPreview, acViewNormal), , Criteria

The best way to catch these types of errors is to run the code in debug mode and look at the value of your variables. Maq
<insert witty signature here>

 

[FoxProProgrammer]

After I posted my last message to you, I noticed an error in the syntax. The following syntax *should* be correct:

DoCmd.OpenReport &quot;MATE Calibration&quot;, IIf(where.Value = 1, acViewPreview, acViewNormal), , &quot;([Cal Due] >= &quot; & Forms!Print!startdate.Value & &quot And ([Cal Due] <= &quot; & Forms!Print!enddate.Value & &quot&quot;

This causes #Error to display in the report output. I'm not sure if I'm going to spend any more time on this because it works the other way. I'm really just curious what I did wrong with the Where clause.

Thanks again.
dz
dzaccess@yahoo.com