By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,574 Members | 3,021 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,574 IT Pros & Developers. It's quick & easy.

SQL error 3075 - string variables not working?

P: 3
I have been coding for a while, and admittedly am a bit of a hack / learn as you go type of person, so I am sure this code is not pretty, any help would be greatly appreciated, I've been stuck for a couple of days on this and have tried all the different ways I know.
The goal of this code is to on click:
1. check to see if a combo box is blank - if it is, then return all values for the associated field
2. if the combo box has a value, pass it to a string value to be used in code to call a query which is displayed in a subform of the main form.

I am receiving an error in my SQL when I try to apply it to the subform / query. However, the same code works when I simply apply a textbox.value in place of the variable - I have a suspicion this is related to quotes, but cannot find my mistake.

Any help would be appreciated. Thanks in advance!
------------------------------------------------------------------------
Private Sub CmdRunQuery_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBuilding As String
Dim strFloor As String
Dim strFEMASysCode As String
Dim strFEMASubSys As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySelectAll")
' determines if combo box is null or has been assigned a value, if it is null, it should return all rows regardless of value for the field

If IsNull(ComboBuilding.Value) Then
strBuilding = " Like '* "
Else
strBuilding = "'" & Me.BuildingText.Value & ""
End If

If IsNull(ComboFloor.Value) Then
strFloor = " Like '* "
Else
strFloor = "'" & Me.FloorText.Value & ""
End If

If IsNull(ComboFEMASystemCSI.Value) Then
strFEMASysCode = " Like '* "
Else
strFEMASysCode = "'" & Me.FEMASystemText.Value & ""
End If

If IsNull(ComboFEMASubSystemCSI.Value) Then
strFEMASubSys = " Like '* "
Else
strFEMASubSys = "'" & Me.ComboFEMASubSystemCSI.Value & ""
End If
'The following line(s) of code is the SQL build from all of the variables (on one line). I have tried it on multiple lines and on a single line, neither work w/ the string variable.
strSQL = "SELECT * FROM wrkReportTotalsUnionAll WHERE wrkReportTotalsUnionAll.[BuildingNumber] = " & strBuilding & " AND wrkReportTotalsUnionAll.[Floor] = " & strFloor & " AND wrkReportTotalsUnionAll.[FEMASystemCSICode] = " & strFEMASysCode & " AND wrkReportTotalsUnionAll.[FEMASubSystemCSICode] = " & strFEMASubSys & "ORDER BY wrkReportTotalsUnionAll.[PK], wrkReportTotalsUnionAll.[Vendor];"

'The following line(s) replace the string w/ a text box value (which has a control source of its related combo box). This line of code works correctly, unless there is a null value (i.e. one of the combo boxes is not used.
'strSQL = "SELECT * FROM wrkReportTotalsUnionAll WHERE wrkReportTotalsUnionAll.[BuildingNumber] ='" & Me.BuildingText.Value & "' AND wrkReportTotalsUnionAll.[Floor]='" & Me.FloorText.Value & "' AND wrkReportTotalsUnionAll.[FEMASystemCSICode]='" & Me.FEMASystemText.Value & "' AND wrkReportTotalsUnionAll.[FEMASubSystemCSICode]='" & Me.FEMASubSystemText.Value & "' ORDER BY wrkReportTotalsUnionAll.[Floor];"

MsgBox (strSQL)
qdf.SQL = strSQL
' test to see if underlying control source query works for the subform
DoCmd.OpenQuery "qrySelectAll"

'Push the query parameters to the subform to display the data results - this works if the textbox.values are used in a query

Forms![FrmDisplayData]![qrySelectAll].Form.RecordSource = strSQL

Set qdf = Nothing
Set db = Nothing

End Sub
Nov 21 '06 #1
Share this Question
Share on Google+
11 Replies


Expert 100+
P: 218
Standard SQL syntax for LIKE is

Expand|Select|Wrap|Line Numbers
  1. LIKE('%')
Note that the wildcard character is % and not *

This may get you started.

HTH

Steve
Nov 21 '06 #2

Expert 5K+
P: 8,434
Standard SQL syntax for LIKE is
...
Note that the wildcard character is % and not *
Is it?! The asterisk has always worked for me when working with Access queries. Perhaps Access allows both, or something?
Nov 21 '06 #3

NeoPa
Expert Mod 15k+
P: 31,494
While that is true for some, more standard, database systems - it is not true for Access.

I suspect that the problem is how you edited your code - or more precisely what you edited it in (Like Word I suspect).
Word has an option for converting simple quotes (') for slanted apostrophes.
Your code has some of these in it in place of quotes.
I only saw this as I had to copy / paste it to a text editor to view it as it was not within CODE tags.
Nov 21 '06 #4

Expert 5K+
P: 8,434
Word has an option for converting simple quotes (') for slanted apostrophes.
Your code has some of these in it in place of quotes.
I only saw this as I had to copy / paste it to a text editor to view it as it was not within CODE tags.
Hey, you're right. Lucky catch, that.

I didn't actually read the code before, but if you look real close, there is a visible difference between some of the single quotes. Nothing you'd ever notice, though. However, if you put CODE tags around it (hint hint, people!), they stand out.

Check this out...
strBuilding = " Like '* "
Expand|Select|Wrap|Line Numbers
  1. strBuilding = " Like '* "
Nov 21 '06 #5

NeoPa
Expert Mod 15k+
P: 31,494
;) .
Nov 21 '06 #6

Expert 5K+
P: 8,434
:) .
Nov 21 '06 #7

NeoPa
Expert Mod 15k+
P: 31,494
That's a pretty workable solution - thanks for the idea.
Nov 21 '06 #8

P: 3
That's a pretty workable solution - thanks for the idea.
So I'm a rookie / hack at the code, but you're telling me the issue is really revolving around some of the single quotes I have for the like '*' statements?

Just trying to make sure I'm fixing the right part of the code - I need this to work, it's the last part of the puzzle so I really am appreciative of the helps...
Nov 22 '06 #9

P: 3
OK - so I went through in my VB editor and reworked all of the ' which were apostrophes (on a slant) replacing them with ' (single quotes)... I have to say that after 2 days of trouble shooting this, I was pretty much ready to throw in the towel, but the experts came up with the solution and I am again forever grateful. So I will end this w/ a wonderful quote about all those who contributed and commented on my post NeoPa, Killer42, even CyberDwarf, in the words of the great Bette Midler, "Did you ever know that you're my Hero!"

Thanks for the help folks I thank you and if FEMA had any class they would thank you too (no I do not work for them). Here is the finalized code, in case anyone else ever has this problem:

Private Sub CmdRunQuery_Click()
'Declare Variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBuilding As String
Dim strFloor As String
Dim strFEMASysCode As String
Dim strFEMASubSys As String
'ID the database and assign it to the variable
Set db = CurrentDb
'Assign query to a variable
Set qdf = db.QueryDefs("qrySelectAll")

'Get values from Combo boxes
If IsNull(ComboBuilding.Value) Then
strBuilding = " Like '*' "
Else
strBuilding = "='" & Me.BuildingText.Value & "' "
End If

If IsNull(ComboFloor.Value) Then
strFloor = " Like '*' "
Else
strFloor = "='" & Me.ComboFloor.Value & "' "
End If

If IsNull(ComboFEMASystemCSI.Value) Then
strFEMASysCode = " Like '*' "
Else
strFEMASysCode = "='" & Me.ComboFEMASystemCSI.Value & "' "
End If


If IsNull(ComboFEMASubSystemCSI.Value) Then
strFEMASubSys = " Like '*' "
Else
strFEMASubSys = "='" & Me.ComboFEMASubSystemCSI.Value & "' "
End If

strSQL = "SELECT wrkReportTotalsUnionAll.* " & _
"FROM wrkReportTotalsUnionAll " & _
"WHERE wrkReportTotalsUnionAll.BuildingNumber " & strBuilding & _
"AND wrkReportTotalsUnionAll.Floor " & strFloor & _
"AND wrkReportTotalsUnionAll.FEMASystemCSICode " & strFEMASysCode & _
"AND wrkReportTotalsUnionAll.FEMASubSystemCSICode " & strFEMASubSys & _
"ORDER BY wrkReportTotalsUnionAll.PK, wrkReportTotalsUnionAll.Vendor;"

MsgBox (strSQL)
'Pass SQL string to query
qdf.SQL = strSQL
qdf.Close
Debug.Print strSQL
'This pushes the same queried data to the subform qrySelectAll
Forms![FrmDisplayData]![qrySelectAll].Form.RecordSource = strSQL
'Not entirely sure if this is necessary, but doesn't seem to hurt either.
Forms!FrmDisplayData.qrySelectAll.Requery

Set qdf = Nothing
Set db = Nothing
Exit Sub

End Sub
Nov 22 '06 #10

NeoPa
Expert Mod 15k+
P: 31,494
It's always nice to be appreciated, and these forums always like it when posters confirm that the problem is solved - so good on you for all that.
May I just suggest, though, and this is also a reminder for all those reading this thread, to use the tags provided for code and other types here.
It does make reading what's posted easier ;).

All the best anyway -NeoPa.

PS. BTW Killer - it was NOT luck TYVM! ;)
Hey, you're right. Lucky catch, that.
Nov 22 '06 #11

Expert 5K+
P: 8,434
First off, thanks for that whulling. However, if you look back over the thread you'll find that all I contributed in this case was a bit of cross-chat. It was NeoPa and cyberdwarf who addressed the issue.

...May I just suggest, though, and this is also a reminder for all those reading this thread, to use the tags provided for code and other types here.It does make reading what's posted easier ;).
You're a moderator, why not edit the CODE tags into the final version for posterity?

PS. BTW Killer - it was NOT luck TYVM! ;)
I don't know what TYVM stands for. But I think it was largely luck. See below...

I only saw this as I had to copy / paste it to a text editor to view it as it was not within CODE tags.
:)
Obviously this indicates to the rest of us "experts" the course of best practice. Normally I'm a good proofreader and do catch a lot of little details that others miss. But I must remember in future - always view the code between CODE tags or in something like Word, to catch these sort of things.
Nov 22 '06 #12

Post your reply

Sign in to post your reply or Sign up for a free account.