syntax error

[slimjen]

All, using access 2010; I have a subform used in a form. I changed the query to filter information needed and now I am getting an error when I use the form. The query runs fine but not when the subform in on the form. Now the subform is all white. The error is: syntax error (missing operator) in query expression. Here’s my query:

SELECT tblMSRP.*, *
FROM tblMSRP
WHERE (((tblMSRP.ID)=DMin("ID","tblMSRP","gen_group='" & [gen_group] & "' AND rate=" & DMax("rate","tblMSRP","gen_group='" & [gen_group] & "'"))));

[ssanfu]

Not sure if your query is a saved query, in VBA or a string in the sub-form record source.

But it look like there is a missing double quote and closing parenthesis. It is easier to see if you remove the parenthesis Access adds...
(I also added a couple of spaces and took out a double quote and the ampersand next to the "DMax" function)

SELECT tblMSRP.* 
FROM tblMSRP 
WHERE tblMSRP.ID = DMin("ID","tblMSRP","gen_group = '" & [gen_group] & "'") AND rate = DMax("rate","tblMSRP","gen_group = '" & [gen_group] & "'");

After re-reading your post a few times, I'm going to say the SQL is the sub-form record source.

Is [gen_group] a control on the main form? If yes, you would need to reference the control something like

"Me.Parent.[gen_group]"

The SQL would look like:

SELECT tblMSRP.* 
FROM tblMSRP 
WHERE tblMSRP.ID = DMin("ID","tblMSRP","gen_group='" & Me.Parent.[gen_group] & "'") AND rate = DMax("rate","tblMSRP","gen_group = '" & Me.Parent.[gen_group] & "'");

Maybe I'm reading this wrong......

[slimjen]

GM. thanks for replying. The recordsource for the subform has a query as the recordsource. gen_group is a control in the subform that is the child link to a master link control on the main form. thanks

[ssanfu]

Do either of the above queries work?

In the 2nd query, you would have to change the "Me.Parent.[gen_group]" to the name of the main form control (Master Link).

[slimjen]

The first works but I get different results which are incorrect.

[slimjen]

I don't understand because when I run the same query in another front end; I get the correct results. The subform shows up correct. Maybe this particular form is hosed up?

[ssanfu]

Wouldn't hurt to delete the sub-form and re-create it.

[slimjen]

I did just that and received the error: Syntax error (missing operator) in query expression ‘gen_group=” AND rate=’. When I click ok; i receive: This expression is typed incorrectly, or it is too complex to be evaluated. For ex. A numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to the variables.

[ssanfu]

OK, I found out I don't know enough about your dB.

I tried to create a test dB w/ forms & queries.. didn't go as planned. I created 2 saved queries. Neither gave errors:

SELECT tblMSRP.*
FROM tblMSRP
WHERE (((tblMSRP.ID)=DMin("ID","tblMSRP","gen_group = '" & [gen_group] & "'")) AND ((tblMSRP.rate)=DMax("rate","tblMSRP","gen_group = '" & [gen_group] & "'")));

SELECT tblMSRP.* 
FROM tblMSRP 
WHERE tblMSRP.ID = DMin("ID","tblMSRP","gen_group='" & Forms!MainForm.[gen_group] & "'") AND rate = DMax("rate","tblMSRP","gen_group = '" & Forms!MainForm.Parent.[gen_group] & "'");

If I took out the ID part of the where clause, I got one record, as I expected. I couldn't get the subform to return records if I had both criteria in the query.

So, not knowing what your main form record source is and not knowing what any of the data looks like, I hit a wall.

I don't understand how a record can have the ID (autonumber??) be the minimum AND (at the same time) the rate be max.


Either one of the queries above will execute without errors. but that is as far as I could get.

[John_G]

Hi -

The error message you are getting suggests that your dMax and/or DMin functions are returning nulls, which means the criteria used in them are not identifying any records, or that the values in the requested fields (ID and Rate) are all Null for a given value of [Gen_group]. Take a look at the table tblMSRP to see if the data is correct.

HTH

John