Type Mismatch 3

[random75]

Access 2000. I've encontered Too Few Parameters, Syntax Errors, and Type Mismatch errors on this code. Right now I have worked through the other errors and I am left with Type Mismatch and I know it relates to the syntax after the last "and" condition. I have tried many alternatives but I just can't seem to get it. I can get the conditions to work individually but not together. Any help would be very much appreciated.

Set rs = CurDB.OpenRecordset("SELECT * from UnprocessedBatchQuantityUsedSummationQuery WHERE" & _
" BatchID =" & [Forms]![ProductionForm]![ProductionSubForm1]!BatchID And ItemUsed = " & Me.ItemUsed & ")

 

[MajP]

for debugging purposes the easiest way to do this is like this

dim strSql as string
strSql = "SELECT * from UnprocessedBatchQuantityUsedSummationQuery WHERE" & _
" BatchID =" & [Forms]![ProductionForm]![ProductionSubForm1]!BatchID And ItemUsed = " & Me.ItemUsed & "
debug.print strSql
Then you will see your problems.

after you get it reading correctly

currentDB.openrecordset (strSql, opendynaset)

(notice currentdb not currdb)

I can tell that this will not resolve properly by looking at it, but if you print it out you can also see the problem

WHERE" & _
" BatchID =" & [Forms]![ProductionForm]![ProductionSubForm1]!BatchID & " And ItemUsed = " & Me.ItemUsed & ")

at a minimum you are missing a parentheses before the AND.

currentdb not curdb

 

[TheAceMan1]

How are ya random75 . . .

Try this (Note: The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.):

[blue]   Dim curDB As DAO.Database, rs As DAO.Recordset, sfrm As Form, SQL As String
   
   Set curDB = CurrentDb
   [purple]sfrm[/purple] = Forms!ProductionForm!ProductionSubForm1[purple][b].Form[/b][/purple]
   
   SQL = "SELECT * " & _
         "FROM UnprocessedBatchQuantityUsedSummationQuery " & _
         "WHERE ([BatchID] = " & [purple]sfrm[/purple]!BatchID & ") AND " & _
               "([ItemUsed] = [red][b]'[/b][/red]" & Me.ItemUsed & "[red][b]'[/b][/red]);"
   Set rs = curDB.OpenRecordset(SQL)[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[random75]

Thanks to both of you for your responses. I have looked at both of your responses and done some research on the built in debugging tools for Access 2000. I played with the code some and used the BreakPoint feature and the closest I have gotten to an error free statement is this...

Set rs = CurDB.OpenRecordset("SELECT * from UnprocessedBatchQuantityUsedSummationQuery WHERE" & _
" BatchID =" & [Forms]![ProductionForm]![ProductionSubForm1]!BatchID And ItemUsed = Me.ItemUsed)

This statement produces correct values for every element except for the BatchID value at the start of the second line. BatchID produces no value in Breakpoint mode. I am still getting the 'type mismatch' error. Any further suggestions?

 

[MajP]

Your sql string is incorrect, and you will see it if you print it out with a debug.print. As both of us pointed out you have an "And" outside of quotes, and if you have strings for values they will need single quotes. This does not look like the Sql string AceMan suggested. Why not just copy his code?

 

[dhookom]

I don't know why you don't take the advice of a couple of the sages and create your SQL in a memory variable. We don't do this just to have to type more. Your code will be more manageable and easier to troubleshoot:

[green]' code assumes BatchID and ItemUsed are numeric[/green]
strSQL = "SELECT * " & _
 "FROM UnprocessedBatchQuantityUsedSummationQuery " & _
 "WHERE BatchID =" & [Forms]![ProductionForm]![ProductionSubForm1]!BatchID & _
 " And ItemUsed = " & Me.ItemUsed
Set rs = CurDB.OpenRecordset(strSQL)

Duane
Hook'D on Access
MS Access MVP

 

[random75]

Please do not get angry. I appreciate your experience and your willingness to help. I am trying to undersytand. MajP, in looking at your instructions, I inserted the code at the same place where the original code was, remarking out everything that was there previously so that only your code is there. Here is that code...

Dim strSql As String
strSql = "SELECT * from UnprocessedBatchQuantityUsedSummationQuery WHERE" & _
" BatchID =" & [Forms]![ProductionForm]![ProductionSubForm1]!BatchID And ItemUsed = Me.ItemUsed
Debug.Print strSql

When I run through the program, I get the same type mismatch error and I am not getting any output in the Immediate Window from the debug.print statement. I did some research, but I was unable to determine the reason. Perhaps I am not understanding your instructions or I am placing this code in the wrong place or I am executing it improperly.

Also in your instructions, I don't know whether the currentDB.openrecordset statement is specific to the print debug process, but when I try to insert that code as you have written it, I get a Compile Error Expected:= response in the window and a syntax error when it tries to compile. I temporarily remarked it out. In trying to understand debug and my ability to use it in the future, I'd like to resolve these issues with your help before I move on to TheAceMan1's suggestions.

 

[random75]

Well, in doing some trial and error, I changed the code slightly and did get output in the Immediate Window from the print debug statement, so the issue is with my code obviously. I will work on that using TheAceMan1's suggestions. What about the compile eror on the currenDB.openrecordset command? Thanks!

 

[random75]

After implementing TheAceMan1's code, the routine is running perfectly. Sorry for the runaround. I have a strong desire to understand rather than just do, which is why I was proceeding on my own trying to use your information to find my solution. I always apreciate not only the efforts of all the experts, but also the incredibly fast response to questions. At your convenience, I am curious to know about the syntax issues with your statement ...

currentDB.openrecordset (strSQL, opendynaset)

which produces the Compile Error Expected:= error and will not compile properly. Thanks again!

 

[dhookom]

The code specifies the type of DAO recordset. The type should actually be dbOpenDynaset.

Duane
Hook'D on Access
MS Access MVP

 

[random75]

I see the error with the openrecordset statement. I need to set a variable. Finished. Thanks all again!

 

[MajP]

lets say [Forms]![ProductionForm]![ProductionSubForm1]!BatchID
returns a value of: "Batch123"
and
Me.ItemUsed returns a value of: "Itm234"

You probably want your sql string to read like this

"SELECT * from UnprocessedBatchQuantityUsedSummationQuery WHERE BatchID = 'Batch123' And ItemUsed = 'Itm234'"

If BatchID is numeric and/or ItemUsed is numeric you would want your string to resolve to something like

"SELECT * from UnprocessedBatchQuantityUsedSummationQuery WHERE BatchID = 123 And ItemUsed = 234

If you get this wrong you will get a type mismatch because you are trying to assign a string to a numeric field or vice versa.

But your problems start here

" BatchID =" & [Forms]![ProductionForm]![ProductionSubForm1]!BatchID And ItemUsed = Me.ItemUsed

At a minimum

" BatchID =" & [Forms]![ProductionForm]![ProductionSubForm1]!BatchID & " And " & ItemUsed = Me.ItemUsed

but since the "and" is not in quotes it is trying to resolve
Batch123 and ItemUsed = Me.ItemUsed
and also gives a type mismatch error.

Example
x = "Cat"
y = "Dog"
debug.print x & " and " & y
' result: "Cat and Dog"
debug.print x and y
' type mismatch

 

[random75]

This explanation was a huge help. Thanks a lot. I hate to just invoke code without understanding the details as to why one works and another doesn't. And while I know the type mismatch is due to inconsistency between string and numerics, understanding how the syntax contributes to the mismatch will help in my ability to resolve these kinds of problems on my own in the future.