VBA or Query which is best?

[Precips1]

Hi - I'm stuck again!!

I'm trying to setup a print preview option for a report based on two dates.

User is asked to select a Date from: and Date to: from two combo boxes.
This bit works fine and gives a report based on the two dates.

In addition to this - I'd like to also be able to select from two radio buttons people that are either {button 1}= [indate] or {button 2} [expired] these are results that are currently stored in my query (qryReportData)

Summary.
User selects two dates: From & To (Works fine)

User selects either radio button indate or expired
This is where I'm stuck - How do I code or query this radio button select into my final report?

End result - Report runs showing people that are either indate or expired
depending on the selected options.

Thanks in advance
Mike

 

[vbaInet]

... AND [FieldName] = IIF([Forms]![[COLOR=Red]NameOfForm[/COLOR]]![[COLOR=Red]FrameControlName[/COLOR]] = 1, "indate", "expired")

indate and expired must be the exact text in which it is saved in the table.

 

[Precips1]

Thanks for your help

Did as you said - Now get this error say MS can't find the field but I don't know which field it means?

This is the full line of code i have to run the report - Can you see where its falling over? The line in bold is your bit added.

DoCmd.OpenReport "rptOrders", acViewPreview, , "[RegistrationDate] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#" _
And [indate] = IIf([Forms]![frmPrintOptions]![Frame38] = 2, "indate", "expired")

Mike

 

[vbaInet]

I thought you were using it directly on the report's record source that was why I gave you the long reference. You can use one of the following:

DoCmd.OpenReport "rptOrders", acViewPreview, , "[RegistrationDate] Between [Forms]![frmPrintOptions]![cboFrom] And [Forms]![frmPrintOptions]![cboTo] " _
                                               "And [indate] = IIf([Forms]![frmPrintOptions]![Frame38] = 2, 'indate', 'expired')"

DoCmd.OpenReport "rptOrders", acViewPreview, , "[RegistrationDate] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "# " _
                                               "And [indate] = '" & IIf(Me.Frame38 = 2, "indate", "expired") & "'"

 

[Precips1]

Still not working - I need to re-think this one. I'm not sure I'm looking in the right places for the data???

I have what I'd called a master query that stores all the data for the report data when asked for "qryReportData". inside this query I have a field called [Indate] which depending on other dates determines if this = "Indate" or "Expired"

Then from a form which is called [frmPrintOptions] I'm trying to select depending on two date combo's [cboFrom] & [cboTo] Also within the selection area, the user can select a radio button within a group frame called [frame38]

Then on a cmd button click opens a report called [rptOrders] which I hoped would show results for the two given dates and either indate or expired data.

I'm sorry if I'm not getting this - But is it best to VB this behind the OnClick button from the form or place it into the query?

VBAInet - Thanks, stay with me buddy.

Mike

 

[vbaInet]

Keep doing it the current way.

So when you say it's not working do you mean it's not showing the desired results on the report or it's throwing an error? If it is throwing an error, what is the exact error message?

 

[Precips1]

VBAInet - Please take a look and see where its all going wrong.

Database attached - Once opened top right "Print options" - this is where the problem lies...

Think you know what I'm trying to do

Again Thanks
Mike

 

[vbaInet]

For example, if I used the first one I gave you it opens up without errors:

DoCmd.OpenReport "rptOrders", acViewPreview, , "[RegistrationDate] Between [Forms]![frmPrintOptions]![cboFrom] And [Forms]![frmPrintOptions]![cboTo] " [COLOR=Red][B]&[/B][/COLOR] _
                                               "And [indate] = IIf([Forms]![frmPrintOptions]![Frame38] = 1, 'live', 'expired')"

There was only a missing & highlighted. And, I don't know why you changed the original IIF() function to 2 when it stands for 'expired'? It should be 1 (as in the code).

Remember to remove what is in the FILTER property of the report.

Just noticed another thing, the InDate field has Live and Expired so why are you putting indate as a value??

 

[Precips1]

Thanks vbaInet

Remember to remove what is in the FILTER property of the report.

Not sure how to turn off the filter in the report - This get assigned from the [frmPrintOptions]somewhere or somehow? Each time I delete the line it come back on the next time the report opens?

Just noticed another thing, the InDate field has Live and Expired so why are you putting indate as a value??

Not sure! Think it may have been in a fit of madness - Thought I needed to use the field title name and the name I'd given to the radio buttons which were called In Date and Expired. Sorry.

Although the onclick now works and goes through without error into the report - Im still not seeing and results for the expired? Select the "In date" radio button and works fine but nothing reports when I select the Expired.

Mike

 

[vbaInet]

Look at the available date ranges in the combo boxes and compare it to the dates available in RegistrationDate. Then look at your query and see if it is ever possible to get Expired results from those date ranges.

 

[Precips1]

HHmmm.... Its a good point - The dates I'm looking at never expire

I've now changed the form for two unbound / un-Raw sourced text'd boxes - user now needs to enter two dates and it works great - even the exired works too and to be honest, probably works best this way - most of the time we'll use this option to look at who needs training between now and the end of the year or over the next 6 months etc.

Listen - Its been top working with you today - WOW! I mean yesterday and some of today!! Thanks for all your help. I know Its been difficult at times for you getting through to me. Got there in the end.

Have a great New Year

Mission Complete!

Can't believe its 6am...been up all night!

Mike

 

[vbaInet]

I'm delighted you figured it out from my explanation.

Glad we could help!

 

[smig]

one tip:
do yourself a favour and replace frame38 with a meaningfull name.