Form not Opening in Parameter query

tb1

New member
Local time
Today, 02:08
Joined
Jun 13, 2012
Messages
3
Hello,

The combo box I am calling in the criteria of a query is not working :banghead:

This is what I've entered.

Criteria: [Forms]![frmOpNameSearch]![cmbOpName]

When I run the query it just opens a "Enter parameter Value" form and treats the criteria I have entered as text. In other words, Access doesn't even recognize it as a form to open. Any suggestions or ideas would be greatly appreciated.
 
The query will not open the form; you need to have it open prior to running the query. Typically you'd have a button on the form to open the form/report using the parameter query.
 
Hi Paul, I wanted to post a question I had that is related to what tb1 posted above. I wanted to know if maybe you could offer some insight as you have helped me out in the past (question for assistance not only directed towards Paul either fellow forum mates-please chime in if you wish).

I have an issue here I could really use some help with. I have approached the issue at multiple angles but nothing seems to work. I am probably overlooking something in the syntax but that’s where your advice would be so appreciated.

To provide some backdrop, I have a form here that a user can select a combo box with a list of dates. Also included on the form are a couple of buttons which run queries upon clicking. I wanted the queries to be based on whatever date the end user selects and if they leave the combo box empty, give me the result set based on the current date.

The syntax I used is below and I have attached a couple screenshots for reference too.

IIf([forms]![MR_Switchboard]![EffectiveDate] Is Null,In (SELECT [DD_MaxEffDate].[MaxOfEffective_Date] FROM DD_MaxEffDate,[forms]![MR_Switchboard]![EffectiveDate])

What I’m saying here is if the combo is null, then I want the last/most recent date in the table to be my criteria, else whatever the end user selects.
So back to the issue, when I click on the button in the form to run the report (the name is "Employee Detail"-see attached), the query opens up but no rows are returned. Also to note here, the combo is blank.
When I select a date in the combo box, the rows with that respective date are returned (which is what I want).

Thanks Paul n’ all.
 

Attachments

  • Query Criteria Form Example 61412.JPG
    Query Criteria Form Example 61412.JPG
    65 KB · Views: 157
The false part is missing a = , because there is field name preceeding the IIF.

Next time, do not "threadnap" a thread but open your own, if the only thing in common with your problem is "query", as it is in this case.
 
I'll make note of posting my own threads next time. I meant no offense and apologize if that upset you spikepl. I wouldn't want to "threadnap" anymore and end up on Oprah.

FYI, the "solution" you posted doesn't work.
 
I am not upset - the point is to keep relevant info together for posterity and making it findable easily.

Also "doesn't work" is a term meaningless to anyone not watching your screen. What does your SQL look like now and what happened or not happened? Error messages? Did the query run? What did it display?
 
The proper way of doing this would be

MyFieldName In (SELECT [DD_MaxEffDate].[MaxOfEffective_Date] FROM DD_MaxEffDate AND [forms]![MR_Switchboard]![EffectiveDate] IS NULL OR MyFieldName = [forms]![MR_Switchboard]![EffectiveDate]
 
Aside from a missing paren after "FROM DD_MaxEffDate", the query appeared to run fine. I tested a couple scenarios selecting a date in the combo vs. leaving it blank and everything ran fine.

Thanks
 

Users who are viewing this thread

Back
Top Bottom