SQL statement in VBA Concatenation (1 Viewer)

[Deleted member 8621]

I am trying for the first time to write a query in VBA because I can't get it to work in query desing do to my SELECT TOP [n] varialbe.
The problem is I can't seem to concatenate within VBA. Example

SQL statement in query desing view that I am trying to use:

WHERE ((([tblMeterData].[Model] & ", SN: " & [tblMeterData].[SN]) Like [Forms]![frmAdminEntry]![Text7]))

When I try to get this statement in VBA it will not take. I have tried singel quote: '", SN: "' and that didn't work.

Text7 looks like this:

Oce 9800, SN: 980101496

where Oce 9800 is the Model and 980101496 is the SN

Help
thanks
matthew

 

[David R]

You shouldn't need VBA, if I understand your question..

Make a calculated field in your query: TextToMatch: [tblMeterData].[Model] & ", SN: " & [tblMeterData].[SN]

Now put the criteria Forms!]![frmAdminEntry]![Text7] in under that field, and try running your query. Also it sounds like you want an exact match, not a Like match.

SN and Model are text fields, right?

 

[Deleted member 8621]

David,
You have been a huge help with this database. My VBA programming began with it.

The reason I can't use a query design query is because I have a control on my from "frmAdminEntry" the has a control "for how many months" and the number that the user puts in is the number of months that I want returned. The only way I could think to do this is with the SELECT TOP [n]. But I can't figure out how to use a varialbe for the [n] unless I'm in VBA. The query I have now works fine. But it returns all entries for the given model, SN. This works for now but In a cople of months it will become tiresome scrolling through all the meter readings.

thanks david
matthew

 

[Deleted member 8621]

It seems like I have solved that problem and I am now onto a new one. This is the module that I made to test the VBA sql statement:

Dim rs As New ADODB.Recordset
Dim vrmo As Integer
Dim strSQL As String

vrmo = Forms!frmAdminEntry!Text26

strSQL = "SELECT TOP " & vrmo & " Model, SN, Date, [1stMeter], [1stMtrRollover], "
strSQL = strSQL & "[2ndMeter], [2ndMtrRollover] "
strSQL = strSQL & "FROM tblMeterData "
strSQL = strSQL & "WHERE ((([Model]"
strSQL = strSQL & " & "", SN:"" & "
strSQL = strSQL & "[SN]) Like [Forms]![frmAdminEntry]![Text7])) "
strSQL = strSQL & "ORDER BY Date DESC;"

'Debug.Print strSQL

rs.Open strSQL, CurrentProject.Connection
MsgBox [Forms]![frmAdminEntry]![Text7]
Do Until rs.EOF
Debug.Print rs!SN
rs.MoveNext
Loop

It was throwing an error because of the previous problem but this format seems to work.
Now my problem is the "rs.open" statement. For some reason it keeps throwing this error:

"No value given for one or more required parameters"

If I put my msgbox statement before the rs.open I get the msgbox. With the msgbox where it is I dont get it.

thanks
matthew

 

[David R]

My ADO is worse than rusty...

It's damn near nonexistent.

However, I'm an avid user of the archives of this forum. I put in "rs.open" into the search engine and got about 28 responses. I think the problem is that you need one or two more arguments for .Open

Check those posts for examples of the .Open code you might want to use.

 

[rockman]

I think your problem still rests in the string concatenation.

The phrase [Forms]![frmAdminEntry]![Text7] should not be directly quoted. Move it outside of the quotes. And then view your strSQL string (Msgbox strSQL) to see if it makes sense.

HTH,
Jeff

 

[rockman]

A Design Suggestion...

I think it might be easier for your end-user (even if you're the end-user) to have a model textbox entry and SN textbox entry. Your SQL statement would then look like:

"WHERE (tblMeterData.Model Like " & [Forms]![frmAdminEntry]![Text7] & ") AND (tblMeterData.SN Like " & [Forms]![frmAdminEntry]![Text8] & ")"



Jeff

 

[Pat Hartman]

Do NOT use Like when you are looking for an exact match. The "like" operator will prevent any optimization of the query and force Jet to read EVERY row in the table whereas if you use the = operator, Jet can use indexes if you have them defined.

An alternative to using the TOP predicate is to use an Order By clause that will place the selections that you are most interested in at the beginning of the recordset rather than at the end. You will probably need to choose the descending option rather than ascending for at least one of the fields in the sort sequence.

Also, since model and SN are separate columns in the table (as they should be), use two controls on the form and eliminate the concatenation operation.

 

[Deleted member 8621]

O.K.
I have changed some things and I have made some progress but can't seem to get around this problem. My Code:

Dim rs As DAO.Recordset
Dim vrmo As Integer
Dim strSQL As String

vrmo = Forms!frmAdminEntry!Text26

strSQL = "SELECT TOP " & vrmo & " Model, SN, Date, [1stMeter], [1stMtrRollover], "
strSQL = strSQL & "[2ndMeter], [2ndMtrRollover] "
strSQL = strSQL & "FROM tblMeterData "
strSQL = strSQL & "WHERE (((tblMeterData.Model)=[Forms]![frmAdminEntry]![Text45])"
strSQL = strSQL & " AND ((tblMeterData.SN)=[Forms]![frmAdminEntry]![Text47])) "
strSQL = strSQL & "ORDER BY Date DESC;"

Debug.Print strSQL

Set rs = CurrentDb.OpenRecordset(strSQL)
'MsgBox vrmo

Do Until rs.EOF
Debug.Print rs!SN
rs.MoveNext
Loop

My sql statement works fine till I get to the Where clause. It says "Too few parameters. Expected 2"
I can make this go away by placing a literal value instead of a reference so I know its just the way I have it here. My Debug.Print looks just as it does in sql view. I have tried to move the Where control references out of the "" but that gave me an Expected operator error....

HELP
matthew

 

[David R]

I think your problem still rests in the string concatenation.

The phrase [Forms]![frmAdminEntry]![Text7] should not be directly quoted. Move it outside of the quotes. And then view your strSQL string (Msgbox strSQL) to see if it makes sense.

HTH,
Jeff

You've still got the Forms! references inside the quotes. As you build your SQL you should be referring to the Form field for values, not referencing it for later.

 

[Deleted member 8621]

I had taken them out before (while very frusterated) but forgot to enclose it in &. I have now done that by assinging a variable and then using the variable in my sql string as follows:

strSQL = "SELECT TOP " & vrmo & " Model, SN, Date, [1stMeter], [1stMtrRollover], "
strSQL = strSQL & "[2ndMeter], [2ndMtrRollover] "
strSQL = strSQL & "FROM tblMeterData "
strSQL = strSQL & "WHERE (((Model)=" & mdl & ") AND ((SN)= " & snum & ")) "
strSQL = strSQL & "ORDER BY Date DESC;"

Now I get "syntax error in query expression. It is reading my sql string as

WHERE (((Model)=Modelname) AND ((SN)=SerialNum))

I think the problem is that I need quotes around my varialbe but need my variable out side my quotes. Hopefully thats not as hard or rediculous as it sounds.

Thanks for sticking with me on this
matthew

 

[Deleted member 8621]

After trying every possible combination of single and double quotes it is FINALLY WORKING CORRECTLY!!!!

On Error GoTo Err_modSqlViewMtr

Dim rs As DAO.Recordset
Dim vrmo As Integer
Dim mdl As String
Dim snum As String
Dim strSQL As String

mdl = Forms!frmAdminEntry!Text45
snum = Forms!frmAdminEntry!Text47
vrmo = Forms!frmAdminEntry!Text26

strSQL = "SELECT TOP " & vrmo & " Model, SN, Date, [1stMeter], [1stMtrRollover], "
strSQL = strSQL & "[2ndMeter], [2ndMtrRollover] "
strSQL = strSQL & "FROM tblMeterData "
strSQL = strSQL & "WHERE (((Model)='" & mdl & "') AND ((SN)= '" & snum & "')) "
strSQL = strSQL & "ORDER BY Date DESC;"

Set rs = CurrentDb.OpenRecordset(strSQL)

Do Until rs.EOF
Debug.Print rs!SN
rs.MoveNext
Loop

Exit_modSqlViewMtr:
Exit Sub
Err_modSqlViewMtr:
MsgBox Err.Description
Resume Exit_modSqlViewMtr
End Sub


Thanks for everyones help. This site has been invaluable to me.

matthew

 

[rockman]

Glad to here of your success!

Good Luck with the rest...