"like" parameter in Union query 2

[simon551]

Hi,
I have a union query of union queries and I'm trying to put in a parameter but it's not working.

Select * FROM qryUNIONPurchases UNION SELECT * From qryUNIONSales
WHERE [CO] Like Forms!frmRegister.txtCo & "*"
ORDER BY TransDate;


The parameter never filters. On background: the field [CO] is a made up field in the underlying queries. There is no table data to back up the field.

Thanks in advance,
Simon

 

[alvechurchdata]

WHERE [CO] Like Forms!frmRegister.txtCo & "*"

If the textbox holds the text "abcde" then the SQL needs to end up be something like:
[TT]
WHERE [CO] Like "abcde*"
[/TT]
but your code isn't supplying that first quote. Try:
[TT]
WHERE [CO] Like "'" & Forms!frmRegister.txtCo & "*'"
[/TT]

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[simon551]

Doesn't work (?)

 

[DotNetGnat]

i think the reason is [CO] is not any field in your database as you mentioned...

how did you form [CO]...just replace [CO] with the definition of [CO]

-DNG

 

[alvechurchdata]

Doesn't work

Sorry about that - I've probably got my quotes plaited up. The solution's going to be somewhere along that line though, you need quotes around the target string of the WHERE clause.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[simon551]

I set up a database that links to 3 very similar databases. There are 3 queries that make up each Union query that this union query is combining. I set up a field in the original query that is CO:myCompany to reference where the data is coming from. I'm not sure how else I could do this.

 

[simon551]

This works!
Select * FROM qryUNIONPurchases WHERE [CO] Like "*" & Forms!frmRegister.txtCo & "" UNION SELECT * From qryUNIONSales
WHERE [CO] Like "*" & Forms!frmRegister.txtCo & ""
ORDER BY TransDate;

Thank you!

 

[PHV]

SELECT * FROM (
Select * FROM qryUNIONPurchases UNION SELECT * From qryUNIONSales
) AS U WHERE U.CO Like Forms!frmRegister.txtCo & "*"
ORDER BY U.TransDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886