VBA Query help

[larryg99]

Hello All...
What am I missing here. The below VBA Sql works in the QBE grid but pops an error of Item Not Found in the Collection when executed by a button on the form. I have 2 combo boxes on a form, and the user can enter search text in either of the fields but not both. When I place a Debug.Print strSQL I copy the output into a blank SQL query and it runs and provides the correct results.

Here is the VBA Sql:

strSQL = "SELECT tbl_AOG_Errors_Master.*, tbl_AOG_Errors_Master.Verification_Date, tbl_AOG_Errors_Master.Processor, tbl_AOG_Errors_Master.Manager, tbl_AOG_Errors_Master.Error_Y_N " & vbCrLf & _
"FROM tbl_AOG_Errors_Master " & vbCrLf & _
"WHERE (((tbl_AOG_Errors_Master.Verification_Date) Between #" & [Forms]![frm_ErrorEntry]![tb_SelectStartDate] & "# And #" & [Forms]![frm_ErrorEntry]![tb_SelectEndDate] & "#) AND ((tbl_AOG_Errors_Master.Processor)= '" & [Forms]![frm_ErrorEntry]![cb_SelectAnalyst] & "') AND ((tbl_AOG_Errors_Master.Error_Y_N)=True)) OR (((tbl_AOG_Errors_Master.Verification_Date) Between #" & [Forms]![frm_ErrorEntry]![tb_SelectStartDate] & "# And #" & [Forms]![frm_ErrorEntry]![tb_SelectEndDate] & "#) AND ((tbl_AOG_Errors_Master.Manager)='" & [Forms]![frm_ErrorEntry]![cb_SelectManager] & "') AND ((tbl_AOG_Errors_Master.Error_Y_N)=True));"

Here is the Debug.print output:

SELECT tbl_AOG_Errors_Master.*, tbl_AOG_Errors_Master.Verification_Date, tbl_AOG_Errors_Master.Processor, tbl_AOG_Errors_Master.Manager, tbl_AOG_Errors_Master.Error_Y_N
FROM tbl_AOG_Errors_Master
WHERE (((tbl_AOG_Errors_Master.Verification_Date) Between #10/23/2017# And #10/28/2017#) AND ((tbl_AOG_Errors_Master.Processor)= '') AND ((tbl_AOG_Errors_Master.Error_Y_N)=True)) OR (((tbl_AOG_Errors_Master.Verification_Date) Between #10/23/2017# And #10/28/2017#) AND ((tbl_AOG_Errors_Master.Manager)='John Doe') AND ((tbl_AOG_Errors_Master.Error_Y_N)=True));

 

[moke123]

looks like somethings missing here

AND ((tbl_AOG_Errors_Master.Processor)= '') AND

the vbcrlf's are not needed. just use the line continuation _

it also looks like you can shorten it to

SELECT  * from tbl_AOG_Errors_Master  where ...

 

[isladogs]

Moke's second point is important.

By using * to grab all fields and then re-typing individual fields you are duplicating them.
This can cause issues which prevent queries running without error

 

[larryg99]

looks like somethings missing here

AND ((tbl_AOG_Errors_Master.Processor)= '') AND

the vbcrlf's are not needed. just use the line continuation _

it also looks like you can shorten it to

SELECT  * from tbl_AOG_Errors_Master  where ...

It's not actually missing... that's the result of only one of the two parameters being entered. Users can search using either of the two fields (controls on the form), but not both. You can see in the "VBA SQL here" section in OP, that both are controls on the form. Also, I remind viewers that the "Debug.Print" output runs just fine (i.e. returns desired results) when placed into a new blank query.

 

[isladogs]

Also, I remind viewers that the "Debug.Print" output runs just fine (i.e. returns desired results) when placed into a new blank query.

Yes I read that but you are still duplicating 4 fields so your query results will have 4 fields labelled Expr1000 or similar.
That may not be an issue when run from the immediate window.
However it might be an issue when run from your form.

Try removing the 4 duplicates.
It won't do any harm and with luck it will fix your error.

 

[moke123]

I would be more inclined to separate out the 2 where clauses along the lines of

(aircode may not be correct syntax, but you get the idea)

if not isnull(YourComboboxControlName) then

 strWhere = " tbl_AOG_Errors_Master.Processor)= '" & [Forms]![frm_ErrorEntry]![cb_SelectAnalyst] & "'" 

 else 

 strWhere = " tbl_AOG_Errors_Master.Manager ='" & [Forms]![frm_ErrorEntry]![cb_SelectManager] & "'"

 end if

strSQL = "SELECT * from tbl_AOG_Errors_Master  WHERE  tbl_AOG_Errors_Master.Verification_Date Between #" & [Forms]![frm_ErrorEntry]![tb_SelectStartDate] & _
"# And #" & [Forms]![frm_ErrorEntry]![tb_SelectEndDate] & "# AND tbl_AOG_Errors_Master.Error_Y_N = True   AND " & strWhere

also note that you can generally remove all the )'s and ('s that the QBE inserts

 

[larryg99]

I would be more inclined to separate out the 2 where clauses along the lines of

(aircode may not be correct syntax, but you get the idea)

if not isnull(YourComboboxControlName) then

 strWhere = " tbl_AOG_Errors_Master.Processor)= '" & [Forms]![frm_ErrorEntry]![cb_SelectAnalyst] & "'" 

 else 

 strWhere = " tbl_AOG_Errors_Master.Manager ='" & [Forms]![frm_ErrorEntry]![cb_SelectManager] & "'"

 end if

strSQL = "SELECT * from tbl_AOG_Errors_Master  WHERE  tbl_AOG_Errors_Master.Verification_Date Between #" & [Forms]![frm_ErrorEntry]![tb_SelectStartDate] & _
"# And #" & [Forms]![frm_ErrorEntry]![tb_SelectEndDate] & "# AND tbl_AOG_Errors_Master.Error_Y_N = True   AND " & strWhere

also note that you can generally remove all the )'s and ('s that the QBE inserts

Moke... I like the idea but I'm still hung up on runtime error of Missing ),], or Item in query expression.

Here is what I'm using now:

If Not IsNull(Me.cb_Manager) Then
strWhere = "tbl_AOG_Errors_Master.Processor = '" & [Forms]![frm_ErrorEntry]![cb_SelectAnalyst] & "'"
Else
strWhere = "tbl_AOG_Errors_Master.Manager = '" & [Forms]![frm_ErrorEntry]![cb_SelectManager] & "'"
End If

strSQL = "SELECT tbl_AOG_Errors_Master.*, tbl_AOG_Errors_Master.Error_Y_N, tbl_AOG_Errors_Master.Verification_Date, tbl_AOG_Errors_Master.Processor, tbl_AOG_Errors_Master.Manager " & _
"FROM tbl_AOG_Errors_Master " & _
"WHERE (((tbl_AOG_Errors_Master.Error_Y_N)= True AND (tbl_AOG_Errors_Master.Verification_Date) Between #" & [Forms]![frm_ErrorEntry]![tb_SelectStartDate] & "# And #" & [Forms]![frm_ErrorEntry]![tb_SelectEndDate] & "#) and " & strWhere

When I put the debug output into the QBE, it throws the same error. If I simply put a ) at the very end, it runs and returns the desired data.

 

[Uncle Gizmo]

strSQL = "SELECT tbl_AOG_Errors_Master.*, tbl_AOG_Errors_Master.Error_Y_N, tbl_AOG_Errors_Master.Verification_Date, tbl_AOG_Errors_Master.Processor, tbl_AOG_Errors_Master.Manager " & _
"FROM tbl_AOG_Errors_Master " & _
"WHERE (((tbl_AOG_Errors_Master.Error_Y_N)= True AND (tbl_AOG_Errors_Master.Verification_Date) Between #" & [Forms]![frm_ErrorEntry]![tb_SelectStartDate] & "# And #" & [Forms]![frm_ErrorEntry]![tb_SelectEndDate] & "#) and " & strWhere

You've got three brackets:- WHERE (((

One bracket closed Here:-
(tbl_AOG_Errors_Master.Error_Y_N)

Leaving 2 brackets :- WHERE ((

Next brackets closes here:-
((tbl_AOG_Errors_Master.Error_Y_N)= True AND (tbl_AOG_Errors_Master.Verification_Date) Between #" & [Forms]![frm_ErrorEntry]![tb_SelectStartDate] & "# And #" & [Forms]![frm_ErrorEntry]![tb_SelectEndDate] & "#)

Leaving 1 bracket :- WHERE (

Without a closing bracket

So you have one bracket not being closed, hence your Error...

 

[moke123]

you should be able to shorten it up

	if not isnull(Me.cb_Manager) then
		strWhere = " Processor = '" & [Forms]![frm_ErrorEntry]![cb_SelectAnalyst] & "'" 
	else 
		strWhere = " Manager = '" & [Forms]![frm_ErrorEntry]![cb_SelectManager] & "'"
	end if

	strSQL = "SELECT * from tbl_AOG_Errors_Master  WHERE  Verification_Date Between #" & [Forms]![frm_ErrorEntry]![tb_SelectStartDate] &  "# And #" & _ 
		[Forms]![frm_ErrorEntry]![tb_SelectEndDate] & "# AND Error_Y_N = True   AND " & strWhere

	debug.print strSQL

or what gizmo says...

 

[larryg99]

you should be able to shorten it up

    if not isnull(Me.cb_Manager) then
        strWhere = " Processor = '" & [Forms]![frm_ErrorEntry]![cb_SelectAnalyst] & "'" 
    else 
        strWhere = " Manager = '" & [Forms]![frm_ErrorEntry]![cb_SelectManager] & "'"
    end if

    strSQL = "SELECT * from tbl_AOG_Errors_Master  WHERE  Verification_Date Between #" & [Forms]![frm_ErrorEntry]![tb_SelectStartDate] &  "# And #" & _ 
        [Forms]![frm_ErrorEntry]![tb_SelectEndDate] & "# AND Error_Y_N = True   AND " & strWhere

    debug.print strSQL

or what gizmo says...

Thanks everyone so much. The above code is working like a charm. You folks are the best !