Question This expression is typed incorrectly, or it is too complex...

[tgroneck]

[Solved] This expression is typed incorrectly, or it is too complex...

This expression is typed incorrectly, or it is too complex...

I have a form that I've created to allow users to do a ad-hoc query on data in a table. Most of the fields are setup as

Like [Forms]![Unusual Login Research]![CustomerName] & "*"

The ones I'm having an issue with are those where I have a drop-down box with options for: >, <, >=, <= and between. I've tried a bunch of iterations of the same criteria and keep getting the error above. Just as a test, i was trying to at least get the between option to work with the following:

[Forms]![Unusual Login Research]![ScoreAtLoginCriteria]+" "+[Forms]![Unusual Login Research]![ScoreAtLogin1] And [Forms]![Unusual Login Research]![ScoreAtLogin2]

Where ScoreAtLoginCriteria is the drop-down, set to "between" and ScoreAtLogin1 and ScoreAtLogin2 are integers.

Does this drop-down scenario make sense to anyone? Any ideas on how to get it to function?

Thanks!

 

[spikepl]

No it doesn't. You can only pass values to query criteria, not SQL language elements.

MyField = 37 ' 37 is a value, "=" is a language element.

To create fancy query criteria on the run you need to remake the WHERE part of the SQL statement in code. For this you either rewrite the entire SQL statement in code (search for Search Form on this site, or on http://www.allenbrowne.com/ ) or you can change the WHERE part of an exitisng saved query using QueryDef object. Google it.

 

[gemma-the-husky]

first you want & rather than + to concatenate strings.

not sure if that on its own makes the difference, though.


you also need the and between the the two scores in inverted commas
& " and " & so you end up with

between value1 and value2 as the final string

----
are you error trapping the final command, or just letting the ms debug engine handle it? error trapping is better.


note that the syntax is different for using text, numbers and dates, in the final SQL statement although you probably are aware of this.

 

[tgroneck]

No it doesn't. You can only pass values to query criteria, not SQL language elements.

MyField = 37 ' 37 is a value, "=" is a language element.

To create fancy query criteria on the run you need to remake the WHERE part of the SQL statement in code. For this you either rewrite the entire SQL statement in code (search for Search Form on this site, or on ) or you can change the WHERE part of an exitisng saved query using QueryDef object. Google it.

That makes sense that you can't pass SQL language elements. I had also tried something along the lines of:

=IIf([Forms]![Unusual Login Research]![CookieNetworkCriteria]="between",Between [Forms]![Unusual Login Research]![ScoreAtLogin1] And [Forms]![Unusual Login Research]![ScoreAtLogin2],IIf([Forms]![Unusual Login Research]![ScoreAtLoginCriteria]=">",>[Forms]![Unusual Login Research]![ScoreAtLogin1],...)

What do you think is the issue there? I'm just comparing the drop-down field instead of using it as my actual SQL element.

I am working in the Expression Builder.

I'm looking into the allenbrowne site you mentioned.

Thanks!

 

[tgroneck]

No it doesn't. You can only pass values to query criteria, not SQL language elements.

MyField = 37 ' 37 is a value, "=" is a language element.

To create fancy query criteria on the run you need to remake the WHERE part of the SQL statement in code. For this you either rewrite the entire SQL statement in code (search for Search Form on this site, or on allenbrowne ) or you can change the WHERE part of an exitisng saved query using QueryDef object. Google it.

After looking at allenbrowne, I decided to get rid of the drop-down and just do a "To:" and "From:" scenario with VBA handling the scenario where only one of the values was entered.

Thanks Again!