Conditions with other queries in 1 query...

[vbjohn]

I have this query in access that has multiple queries in it. How can I put a condition on the VB side so I can call the Access Report with the condition. There is this one query which is the main query called "Main" that all the other queries run from. I want to select a range of Employee Numbers. Is there a way to do it? If so how can I do it from the VB Side?

Please look at the image I you do not know what I am talking about.

John-

 

[Opengrave]

Not exactly sure what you are asking. Are you asking how to do a parameterized query in VB? Where will the parameter be coming from - text boxes, combo boxes?

Post your SQL for the query and exactly what you are trying to accomplish and I'll see what I can do to help.

 

[Opengrave]

Oh and it is probably easier without VB just maybe not as pretty....

 

[vbjohn]

On that image as I have shown. The Query called Main is the main query that I am running...

I am using TEXTBOXES.

I would need to use the TEXTBOXES and then click on an enter button to run the query...

strWhere = "(dbo_UPR00100.EMPLOYID BETWEEN '" & txtSEmpNum.Text & "' AND '" & txtEEmpNum.Text & "')"


Set appAccess = New Access.Application

appAccess.Visible = False
appAccess.OpenCurrentDatabase App.Path & "\MNVBDynamics.mdb", True

With appAccess.DoCmd
.OpenReport "W2Summary", acViewPreview, , strWhere
.Maximize
End With

appAccess.Visible = True


The error I get is:

"The specified feild "UPR00100.EMPLOYID" could refer to more than 1 table listed in the FROM clause of your SQL Statement."

I hope that I made it more clear.


John-

 

[Opengrave]

The problem is that more that one table has the EMPLOYID and it can't determine which it should apply the stWhere.

Not sure if this will work in your exact case without a bit of rework but try adding the 'between' stuff to the query itself. In the SQL below I tell the query to get values between the values from the text boxes. I just did a simple one table query but the concept doesn't change, as long as you put the 'between' clause in the right query column it will work.

My table is named Main and my form is named frm_Main.

SELECT Main.EMPLOYID, Main.Name
FROM Main
WHERE (((Main.EMPLOYID) Between [Forms]![frm_Main]![txtSEmpNum] And [Forms]![frm_Main]![txtEEmpNum]));

I hope this photo works....

 

[Opengrave]

here is the photo

 

[vbjohn]

thanks!

Thanks OPENGRAVE! It works.. Nice UserName!!!!!