Can not get Access 2016 queries to accept form value input or TempVars! input 2

[TheresAlwaysAWay]

I've been working in Access 2007 forever and have a myriad of places where a form input is used as a query parameter. In the Criteria line I simply put [Forms]![FormName]![FieldName] and that's all that's ever been necessary. That has also been true of a temporary variable. I only had to put [TempVars]![VariableName] in the Criteria line and everything worked as expected.

I've switched to Access 2016 and I can't get it to accept those Criteria. I get an error message that says, "The Microsoft Access Database Engine does not recognize " as a valid field."

I've done this so often in the past that I just can't understand what the issue could be. I'm hoping one of you wizards can point me in the right direction.

As always, thanks in advance for any replies.

 

[dhookom]

Does this occur in all of your Access files? Does your code/VBA compile? Have you tried the same file on another computer?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

There is only one Access file I use…the main database file for the business.

Yes, the code compiles.

We are on a SQL Server back end and we recently closed the physical office. The entire business is being run remotely so there is no other computer to test it on.

 

[dhookom]

Are you able to create another Access file to test? Are you using a remote desktop? What happens if you temporarily replace the reference with a hard-coded value? There are some recent bugs listed on Karl's Access Forever site.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

I replaced the variable by manually entering exactly the same text as the variable and it works fine.

However I did as you suggested and tested it on another query and it worked normally.

Thank you. I now have a direction to pursue. That’s a big help. I will trace it and let you know what I find.

 

[TheresAlwaysAWay]

I now know much more than I did. I don’t have the answers yet, but I understand more about the problem.

The sources for the Select Query I am trying to limit are two Select Queries and a Crosstab Query. I have found that if I remove the Crosstab from the source things work normally.

I was thrown off, because this was the very first operation I tried to do with Access 2016, and I did not expect that a cross tab query might be the culprit. I just assumed it had to be a difference in versions.

Do you have any suggestions based on the new information?

 

[dhookom]

The crosstab query requires the data type of the parameter. In the query design, select Parameters from the query properties and enter your expression and data type.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

Thank you so much. This solved the issue.

I can’t tell you how much I appreciate the assistance.

 

[TheresAlwaysAWay]

I am still not able to get this to work correctly. I'm using a variable called [TempVars]![VehicleTypes]. Running that value through Immediate Window reveals Like 'RV'. If I type that exact text into the Query Design window it reveals the dataset I'm looking for. However, the temporary variable returns no data at all.

I've tried all the different Data Types that make sense, Long and Short Text and Value, and I still get no return. Do you have any suggestions?

 

[dhookom]

You can’t include the operators (like or =, etc) in the variable. Also, with Like there is almost always an “*”.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

I will test. Which Data Type should I use for the Parameter? Longest form would be RV Or MC Or BT

 

[TheresAlwaysAWay]

OK, thank you. I have it working now with Value as the Data Type and the variable value as just RV. However, if I try to add a type to the list, with either RV Or MC, or 'RV' Or 'MC', it no longer works and returns no data.

For test, I changed the RV that works to 'RV' and tried again. This time it does not work. So with one value it works, but not with several. Do you have any ideas?

 

[TheresAlwaysAWay]

Just FYI, changing the Data Type from Value to either Short or Long Text makes no difference. The configurations that didn't work still don't work.

 

[TheresAlwaysAWay]

We're an insurance agency, and the designations represent vehicle types. RV of course, BT is Boat, and MC is Motorcycle. Trying to limit the list to whatever the selected vehicle type(s) might be.

 

[TheresAlwaysAWay]

One more thing I found. If I type in 'RV' Or 'BT' it works. If I type in RV Or BT it automatically encloses the criteria in double quotes and it still works.

 

[Andrzejek]

Hopefully you have a table, something like:

[pre]
ID ABR DESCRIPTION
1 RV Recreational Vehicle
2 BT Boat
3 MC Motorcycle
4 PL Plane
[/pre]
so you can use something like [tt][blue]VehicleID IN (1, 2, 3)[/blue][/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TheresAlwaysAWay]

What is the appropriate Data Type for the IN expression?

 

[dhookom]

You can’t use the In() with multiple parameters just like my previous reply. I always [link faq701-7433]use VBA to change the SQL of a saved querydef[/url] or create a WHERE CONDITION for DoCmd.Open…

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

Well, I hit it with brute force trial and error, and I finally beat it into submission and got it to do what I want it to do. I actually set the Criteria to be TempVars!BT (BT) Or TempVars!RV (RV) Or TempVars!MC (MC). That satisfied whatever demons were stopping things from working with multiple criteria. Of course, I have to have three variables, and there are Radio Buttons on my pop-up form when the action is called for user to select records to display, and a bunch of crap to describe A only or B only or C only or A+B only or A+C only...etc, but the long and the short is that as ugly as it is, it works. No one but me and you guys will ever know just how ugly and convoluted it is. It gets the desired results, so the boss man will be happy.

Now, after this last nightmare I don't know if this is even reasonable to ask, but I'm gonna throw it out there.

The owner would ideally like to run the report that would restrict not only vehicle type but also State. In other words, show all MC in CA, GA, and FL; or all RV in NY, NM, AZ, and KY...or all MC and RV in UT, TN, WI, and MI. Given the difficulty I had with multiple criteria, I may have to restrict it to only one state, but still, I'm asking whether you can see a way to get me to the ideal of selecting any desired vehicle combination from any selected states.

Regardless of whether it's possible I surely appreciate all the help.

 

[dhookom]

I would use a multi-select list box for these and build a where condition for the DoCmd.OpenReport method. There are several FAQs in this forum that reference list boxes. I typically loop through the selected items in VBA to create a string like:

"WHERE [Color] IN ('Red','Green','Yellow')"

I have a generic function I use BuildIn() that accepts a listbox object, field name, and data type and builds the where string:

Then your code to open the report would look like:

Dim strWhere as String
strWhere = BuildIn(Me.lboColor, "Color",String)
DoCmd.OpenReport "rptYourReportName", acViewPreview, , strWhere

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016