sql creates a parameter rather than a criteria

[gint32]

Hi Everyone,

Can Anyone explain to me where I am going wrong, I am having some great difficulty with a query named "qry_Query1" that gets recreated using the below code.
I have a form with a combox that selects a value then actions on the event with the below code to filter records based on the value the users selects, sounds simple enough, but instead of getting any results I end up creating a parameter query as below:
Anyone that can throw some light on my VBA Sql errors I would be most grateful.



The VBA code I have so far is as follows"

Private Sub Command7_Click() 
Dim db As DAO.Database
Dim qd As QueryDef
Dim vWhere As Variant
Stop
Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete "qry_Query1"
On Error GoTo 0

vWhere = Null

'Apptsurname = field

vWhere = vWhere & " AND ApptSurname=" + Me.cboPayeeID   '''selected combobox value 

 Set qd = db.CreateQueryDef("qry_Query1", "SELECT * FROM tblAppointments WHERE " & _
    Mid(vWhere, 6)) 

    db.Close
    Set db = Nothing
    Stop
    DoCmd.OpenQuery "qry_Query1", acViewNormal, acReadOnly
End If
End Sub

This is the sql of qry_Query1

SELECT *
FROM tblAppointments
WHERE (((tblAppointments.[ApptSurname])=[Smith]));

Below is what I would like to achieve but without any success for "qry_Query1"

SELECT *
FROM tblAppointments
WHERE (((tblAppointments.[ApptSurname])="Smith"));

[Micron]

Looks to me like you are attempting to do way too much for the goal at hand. That is, to filter a form that is already open? I can assist with the sql statement, but have to wonder why you're creating an AND parameter, then ignoring it with the Mid function. For what you are doing, the + sign is also a no-no. You might also be mixing the payee id and surname as different data types?

[gint32]

Sorry, I should have mentioned in the first post, this code is begged, stole and amended from doctor Google.Com:

So, the reference to Me.cboPayeeID is actually not to an Id (any longer) it actually refers to a selected text value, nad in the case of the examples I gave above "Smith"


In short, I just need help with getting rid of the Parameters as I said.

I have "No idea: as to what the Mid is actually doing, if i did then i probably wouldn't need any help

Thanks anyway, for your input

[gint32]

Looks to me like you are attempting to do way too much for the goal at hand. That is, to filter a form that is already open? I can assist with the sql statement, but have to wonder why you're creating an AND parameter, then ignoring it with the Mid function. For what you are doing, the + sign is also a no-no. You might also be mixing the payee id and surname as different data types?

So in essence are you saying if I get rid of the AND part in the example code supplied then I will loose the parameter in the query and it'll work the way I wish is to work
as in

SELECT *
FROM tblAppointments
WHERE (((tblAppointments.[ApptSurname])="Smith"));

[Micron]

Well, you didn't tell me what the sql statement is for. Assuming it is to apply a filter to the existing form, since you appear to be running it off of a command button, this would suffice:

Private Sub Command7_Click() 

Me.Filter = "SELECT * FROM tblAppointments WHERE tblAppointments.[ApptSurname]='" & Me.cboPayeeID & "'"
Me.FilterOn = True

End Sub

Since you've stated the combo is now providing a text value, the combo value is wrapped in single quotes. If the combo field is no longer looking at the name field (as opposed to maybe the record ID field), you'll have to change my reference to the correct field name.
It is also possible to apply this filter after the combo box selection, but you'd have to provide more info as to what exactly you are attempting to do. Even if this is to open another form based on a sql statement, you still have way too much code, but what I provided won't do that either. There is also no provision for removing the filter once it has been applied.

If all you want is to eliminate the prompt, the sql syntax I posted should take care of that, as long as all the name references are correct. If it doesn't do the trick, you'll have to provide more info about that prompt.

EDIT

So in essence are you saying if I get rid of the AND part in the example code supplied then I will loose the parameter in the query and it'll work the way I wish is to work
as in

I think so, as long as the name references are correct and you concatenate (not add + ) the necessary parts.

[gint32]

I'll try and explain again:
the code as follows(to which i have no idea as to how it works....novice )


Gives me .....A query

SELECT *
FROM tblAppointments
WHERE (((tblAppointments.[ApptSurname])=[Smith]));

"All" I am asking for is help Changing the VBA/SQL Code so as it doesn't give the above but gives the below

SELECT *
FROM tblAppointments
WHERE (((tblAppointments.[ApptSurname])="Smith"));

Don't ask me how or why it does this, As I have no idea, all I know is I just need to change it from a parameter query top to the above example!!!
Hope fully this will make more sense.

Private Sub Command7_Click() 
Dim db As DAO.Database
Dim qd As QueryDef
Dim vWhere As Variant
Stop
Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete "qry_Query1"
On Error GoTo 0

vWhere = Null

'Apptsurname = field

vWhere = vWhere & " AND ApptSurname=" + Me.cboPayeeID   '''selected combobox value 

 Set qd = db.CreateQueryDef("qry_Query1", "SELECT * FROM tblAppointments WHERE " & _
    Mid(vWhere, 6)) 

    db.Close
    Set db = Nothing
    Stop
    DoCmd.OpenQuery "qry_Query1", acViewNormal, acReadOnly
End If
End Sub

[aytee111]

Dim qdf As QueryDef, vWhere as string
Set qdf = CurrentDb.QueryDefs("qry_Query1")
vWhere = " AND ApptSurname=" + Me.cboPayeeID
qdf.SQL = SELECT * FROM tblAppointments WHERE (((tblAppointments.[ApptSurname])='" & Mid(vWhere, 6) & "'));"
Set qdf = Nothing
DoCmd.OpenQuery "qry_Query1"

Not too sure how to get the surname - fix code as necessary.

[Micron]

sorry to not comprehend, but I don't see any difference between

SELECT * FROM tblAppointments
WHERE (((tblAppointments.[ApptSurname])=[Smith]));

and

SELECT *
FROM tblAppointments
WHERE (((tblAppointments.[ApptSurname])="Smith"));

Nor do I see what makes your query prompt for a parameter, unless as I've already noted, there is a disconnect between what your query expects and the reference you are using to pass the person's name to your query. You can try the sql I already created for you, as in

Set qd = db.CreateQueryDef("qry_Query1", "SELECT * FROM tblAppointments WHERE tblAppointments.[ApptSurname]='" & Me.cboPayeeID & "'")

As long as the combo box is providing a text value and not some number. Again, the whole thing looks overblown - like killing a fly with a sledgehammer.

aytee111: Smith is just an example as per post #3. The name has to come from the combo box (unless I'm totally off base).

[gint32]

Dim qdf As QueryDef, vWhere as string
Set qdf = CurrentDb.QueryDefs("qry_Query1")
vWhere = " AND ApptSurname=" + Me.cboPayeeID
qdf.SQL = SELECT * FROM tblAppointments WHERE (((tblAppointments.[ApptSurname])='" & Mid(vWhere, 6) & "'));"
Set qdf = Nothing
DoCmd.OpenQuery "qry_Query1"

Not too sure how to get the surname - fix code as necessary.

Thanks for you and everyone's input, but when I copy and paste over I get syntax error on this line

qdf.SQL = SELECT * FROM tblAppointments WHERE (((tblAppointments.[ApptSurname])='" & Mid(vWhere, 6) & "'));"

[gint32]

sorry to not comprehend, but I don't see any difference between

SELECT * FROM tblAppointments
WHERE (((tblAppointments.[ApptSurname])=[Smith]));

and

SELECT *
FROM tblAppointments
WHERE (((tblAppointments.[ApptSurname])="Smith"));

Nor do I see what makes your query prompt for a parameter, unless as I've already noted, there is a disconnect between what your query expects and the reference you are using to pass the person's name to your query. You can try the sql I already created for you, as in

Set qd = db.CreateQueryDef("qry_Query1", "SELECT * FROM tblAppointments WHERE tblAppointments.[ApptSurname]='" & Me.cboPayeeID & "'")

As long as the combo box is providing a text value and not some number. Again, the whole thing looks overblown - like killing a fly with a sledgehammer.

aytee111: Smith is just an example as per post #3. The name has to come from the combo box (unless I'm totally off base).

Hi, Sorry the difference between the 1st SQL example and the second is 1st = [parameter] and the other looks for criteria "value", So I am trying to lose the [] and instead get "" from my sub

[Micron]

I am trying to lose the []

I can't believe I didn't see that - and I missed it even without imbibing. I guess it was just too late at night.

Yes, the [] will prompt you because that is how you'd specify a field, and Access would want to know what that is since it can't figure it out. As for your most recent issue, the string you are passing to the variable has to be entirely wrapped in quotes OR the concatenated parts each have to be wrapped in quotes. If how you posted it is how you really have it, I'd say that's the immediate issue.
"SELECT * FROM tblAppointments WHERE (((tblAppointments.[ApptSurname])='" & Mid(vWhere, 6) & "'));"
As I mentioned before, I suspect this will still not work because of your vWhere part. That will include a solitary " (after ApptSurname), plus you are using an arithmetic operator when you should not be. The solution for this was already posted by me, if I recall.

[aytee111]

Missing the beginning quote, my bad. As stated previously, vWhere is an unknown variable.

qdf.SQL = "SELECT * FROM tblAppointments WHERE (((tblAppointments.[ApptSurname])='" & Mid(vWhere, 6) & "'));"

[Micron]

Forget the notion that a double quote will be included.

[ssanfu]

I have "No idea: as to what the Mid is actually doing, if i did then i probably wouldn't need any help

This is what the Mid() function does: The Mid() function copies a sub-string of the specified string starting at the "start" character for "length" characters.
If the "length" value is missing, then the sub-string copied is from the "start" character to the end of the string.

--------
From HELP:
Mid Function : Returns a Variant (String) containing a specified number of characters from a string.
Syntax: Mid(string, start[, length])
where
string Required.
start Required
length Optional; Variant (Long).
--------

Example: Mid("John Wilkes Booth", 6, 6) returns "Wilkes"
Example: Mid("John Wilkes Booth", 6) returns "Wilkes Booth" '<-- because of the missing optional length parameter


You have

vWhere = vWhere & " AND ApptSurname='" & Me.cboPayeeID & "'"

 Set qd = db.CreateQueryDef("qry_Query1", "SELECT * FROM tblAppointments WHERE " & Mid(vWhere, 6))

If vWhere = " AND ApptSurname= 'Smith'", then
Mid(vWhere, 6) is equal to "ApptSurname= 'Smith'"

The Mid() function copies the string at character #6 until the end of the string.
This results in removing the leading space, the word AND and the trailing space in your variable "vWhere".



Since you only have one criteria, you don't need " AND " in your string..... then you don't need the Mid() function!

vWhere = vWhere & "ApptSurname='" & Me.cboPayeeID & "'"

 Set qd = db.CreateQueryDef("qry_Query1", "SELECT * FROM tblAppointments WHERE " & vWhere)

[Micron]

I don't get why it's assigned to vWhere at all. That assignment comes from a reference to a form control. I used that reference in post #5 and noted it should work as written as long as the control was returning text and not a number as cboPayeeID suggests it is.

Me.Filter = "SELECT * FROM tblAppointments WHERE tblAppointments.[ApptSurname]='" & Me.cboPayeeID & "'"
Kudos for you for taking the time to explain the Mid function!