Solved Query that accepts parameters from form

[mamradzelvy]

Hi,
I'm trying to make a query which i would later export to excel, however i got a form where i want to be able to give selection options such as date, yes/no fields, client name etc.
Right now I'm trying to make just the client name combo box work with the query, but i think i got my code wrong, i tried various adaptations, but neither has worked thus far.

SELECT Tabule1.TabKlient, Tabule1.TabOsoba, Tabule1.TabItem, Tabule1.TabQty, Tabule1.TabNote, Tabule1.TabAddedBy, Tabule1.TabDate
FROM Tabule1
WHERE TabKlient LIKE '" & Forms.formExportExcel.xKlientSelection & "';

for instance

or

SELECT Tabule1.TabKlient, Tabule1.TabOsoba, Tabule1.TabItem, Tabule1.TabQty, Tabule1.TabNote, Tabule1.TabAddedBy, Tabule1.TabDate, Tabule1.TabStatus, Tabule1.ID
FROM Tabule1
WHERE (((Tabule1.TabKlient)=[Formuláře]![formExportExcel]![xKlientSelection]));

How would i write the query syntax properly to make this work?

 

[arnelgp]

you do it in the Query builder so you can get the correct syntax.

 

[mamradzelvy]

in the second piece of code the "[Formuláře]" part is just a translation made by the query wizard in my language.

 

[mamradzelvy]

you do it in the Query builder so you can get the correct syntax.

I did! i select the criteria and there i found the combobox i use for the client selection but that has not worked for me. (the second code is made by the query builder)

 

[theDBguy]

I did! i select the criteria and there i found the combobox i use for the client selection but that has not worked for me. (the second code is made by the query builder)

Hi. What is the row source for your combobox?

 

[arnelgp]

check which Column on the combo is the client name. if it is not the Bound column, you might just add an Unbound txtbox and on the AfterIpdate event of the combo set the value of the unbound tbox to the value of that column. use the txtbox as criteria of the query.
you may need to set the Visible prop of the textbox to No.

 

[mamradzelvy]

Hi. What is the row source for your combobox?

Hi, it's SELECT dbKlient.KlientName FROM dbKlient; which for some mysterious reason stopped working just now, ill investigate.

 

[theDBguy]

Hi, it's SELECT dbKlient.KlientName FROM dbKlient; which for some mysterious reason stopped working just now, ill investigate.

Right. If the Combobox is not working, then the query won't work either.

 

[mamradzelvy]

Right. If the Combobox is not working, then the query won't work either.

apparently i've had it set up to 2 collumns instead of 1 for some reason, which caused it not to display what i wanted, don't understand how that happened, but ok.
this works now, which is great!
buuut, i have more questions: for instance, how do i set it up so that i can also disregard the combobox and have it pull all clients? i thought if i force the combo box to be empty, it would just look for everything, but that's clearly not how it works...

 

[mamradzelvy]

Right. If the Combobox is not working, then the query won't work either.

And the query keeps deleting its contents for some reason.... any idea why that could be?

 

[theDBguy]

apparently i've had it set up to 2 collumns instead of 1 for some reason, which caused it not to display what i wanted, don't understand how that happened, but ok.
this works now, which is great!
buuut, i have more questions: for instance, how do i set it up so that i can also disregard the combobox and have it pull all clients? i thought if i force the combo box to be empty, it would just look for everything, but that's clearly not how it works...

You can change the criteria to:

Forms!FormName.ControlName OR Forms!FormName.ControlName Is Null

 

[arnelgp]

or the criteria:

clientNameField =IIF(Trim( Forms!Formname!combo & "") = "", clientNameField, Forms!Formname!combo)

 

[mamradzelvy]

You can change the criteria to:

Forms!FormName.ControlName OR Forms!FormName.ControlName Is Null

Hi,
I had problems with this at first, but it turned out to be because i had the field default at "" instead of Null, thank you!

 

[theDBguy]

Hi,
I had problems with this at first, but it turned out to be because i had the field default at "" instead of Null, thank you!

Hi. Glad to hear you got it sorted out. Good luck with your project.