Multiple If statements in the criteria of a query

[RobUCSD via AccessMonster.com]

I'm trying to instert the following in the criteria section of a query in
query builder; the itself is the record source for a list box
*******************************************************************************************************
If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = "Ablation"
Then

Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _
"WHERE fldRNnotesCode = 'A'" & "ORDER BY [fld.order]"

Else: If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType
= "Device" Then

Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU "
& _
"WHERE fldRNnotesCode = 'D'" & "ORDER BY [fld.order]
"

End If
End If
****************************************************************************************************
I started this by placing it in the onOpen event of the form, but then I
realized I don't want to hard code this in the form. I'd rather to just be
able to do it within a query. Then if I add new VisitTypes I can simply
modify the query without touching the code in the form.

I know my criteria statement is wrong, therefore your help is greatly
appreciated. Rob

 

[Douglas J. Steele]

First of all, your existing code is incorrect: you're missing a space
between the condition, and the ORDER BY clause.

If those are the only two options, try creating the following query, and
using it for as the RowSource of your listbox:

SELECT * FROM tblRNnotesLU
WHERE fldRNnotesCode =
Left(Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType, 1)
ORDER BY [fld.order]

 

[Guest]

Try this --
SELECT tblRNnotesLU.*
FROM tblRNnotesLU
WHERE (((tblRNnotesLU.fldRNnotesCode)=[Enter Code]))
ORDER BY tblRNnotesLU.Order;

 

[Guest]

My Access 2002 SP3 will not let me create a field named fld.order so as to
sort on.

--
KARL DEWEY
Build a little - Test a little

First of all, your existing code is incorrect: you're missing a space
between the condition, and the ORDER BY clause.

If those are the only two options, try creating the following query, and
using it for as the RowSource of your listbox:

SELECT * FROM tblRNnotesLU
WHERE fldRNnotesCode =
Left(Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType, 1)
ORDER BY [fld.order]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to instert the following in the criteria section of a query in
query builder; the itself is the record source for a list box
*******************************************************************************************************
If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType =
"Ablation"
Then

Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _
"WHERE fldRNnotesCode = 'A'" & "ORDER BY [fld.order]"

Else: If
Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType
= "Device" Then

Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU
"
& _
"WHERE fldRNnotesCode = 'D'" & "ORDER BY
[fld.order]
"

End If
End If
****************************************************************************************************
I started this by placing it in the onOpen event of the form, but then I
realized I don't want to hard code this in the form. I'd rather to just be
able to do it within a query. Then if I add new VisitTypes I can simply
modify the query without touching the code in the form.

I know my criteria statement is wrong, therefore your help is greatly
appreciated. Rob

 

[RobUCSD via AccessMonster.com]

Thanks Doug, actually there are 5 different visit types and each type can
have more than one code, i.e., Ablation = A and E. I only wrote re: the 2 to
make things easier. Once I have the syntax down for one type I can do the
others.

I'd rather do this in a query as there maybe future visit types. Then I can
alter the query and not the code. So back to my original post, can I place
the criteria, based on frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType
in the criteria of the field from my initial query that populates the list?
Hope this makes sense. Thanks, Rob

First of all, your existing code is incorrect: you're missing a space
between the condition, and the ORDER BY clause.

If those are the only two options, try creating the following query, and
using it for as the RowSource of your listbox:

SELECT * FROM tblRNnotesLU
WHERE fldRNnotesCode =
Left(Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType, 1)
ORDER BY [fld.order]

I'm trying to instert the following in the criteria section of a query in
query builder; the itself is the record source for a list box

[quoted text clipped - 27 lines]

I know my criteria statement is wrong, therefore your help is greatly
appreciated. Rob

 

[RobUCSD via AccessMonster.com]

Is this to go in the criteria of the underlying query. if so, it doesn't work.
I get "subquery can't return multiple records."

Again, I need to use If Then statements in a query's criteria. thank for your
help and I look forward to anymore suggestions. Thanks, rob

Try this --
SELECT tblRNnotesLU.*
FROM tblRNnotesLU
WHERE (((tblRNnotesLU.fldRNnotesCode)=[Enter Code]))
ORDER BY tblRNnotesLU.Order;

I'm trying to instert the following in the criteria section of a query in
query builder; the itself is the record source for a list box

[quoted text clipped - 23 lines]

I know my criteria statement is wrong, therefore your help is greatly
appreciated. Rob

 

[RobUCSD via AccessMonster.com]

Ok Doug, here's my latest try. This goes in the criteria section in my query
qryRNnotesLU.fldRNnotesCode. Of course I get syntax errors. Could you please
help if you have a chance. Thanks, Rob

If (Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = "Ablation")
Then
WHERE "fldRNnotesCode = 'A' AND 'E'"

First of all, your existing code is incorrect: you're missing a space
between the condition, and the ORDER BY clause.

If those are the only two options, try creating the following query, and
using it for as the RowSource of your listbox:

SELECT * FROM tblRNnotesLU
WHERE fldRNnotesCode =
Left(Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType, 1)
ORDER BY [fld.order]

I'm trying to instert the following in the criteria section of a query in
query builder; the itself is the record source for a list box

[quoted text clipped - 27 lines]

I know my criteria statement is wrong, therefore your help is greatly
appreciated. Rob

 

[Douglas J. Steele]

I'm not sure what you mean by "in the criteria section in my query".

Are you saying you've typed that into the criteria cell in the graphical
query builder? It's not a valid If statement (there's no End If), but even
if it were, you can't put VBA into a query like that. Additionally
"fldRNnotesCode = 'A' AND 'E'" is invalid syntax (it would likely need to be
"fldRNnotesCode = 'A' AND fldRNnotesCode = 'E'", but that won't return
anything, since it's not possible for there to be a row which has two
different values for the same field.)

How about explaining in words what you're trying to do? As you saw, your
first attempt to describe was a little to abbreviated, since what I posted
in response to the literal question apparently wasn't sufficient to meet all
your needs.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok Doug, here's my latest try. This goes in the criteria section in my
query
qryRNnotesLU.fldRNnotesCode. Of course I get syntax errors. Could you
please
help if you have a chance. Thanks, Rob

If (Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType =
"Ablation")
Then
WHERE "fldRNnotesCode = 'A' AND 'E'"

First of all, your existing code is incorrect: you're missing a space
between the condition, and the ORDER BY clause.

If those are the only two options, try creating the following query, and
using it for as the RowSource of your listbox:

SELECT * FROM tblRNnotesLU
WHERE fldRNnotesCode =
Left(Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType, 1)
ORDER BY [fld.order]

I'm trying to instert the following in the criteria section of a query
in
query builder; the itself is the record source for a list box

[quoted text clipped - 27 lines]

I know my criteria statement is wrong, therefore your help is greatly
appreciated. Rob

 

[RobUCSD via AccessMonster.com]

I'm not sure what you mean by "in the criteria section in my query".

Are you saying you've typed that into the criteria cell in the graphical
query builder?

Yes!
It's not a valid If statement (there's no End If), but even

if it were, you can't put VBA into a query like that. Additionally
"fldRNnotesCode = 'A' AND 'E'" is invalid syntax (it would likely need to be
"fldRNnotesCode = 'A' AND fldRNnotesCode = 'E'", but that won't return
anything, since it's not possible for there to be a row which has two
different values for the same field.)

Here's the sql statement as it was before I tried to insert the If statement
in the criteria cell of fldRNnotesCode:

SELECT tblRNnotesLookUp.fldRNnotesLUno, tblRNnotesLookUp.fldNote,
tblRNnotesLookUp.fldRNnotesCode, tblRNnotesLookUp.fldOrder
FROM tblRNnotesLookUp
WHERE (((tblRNnotesLookUp.fldRNnotesCode)="A" Or (tblRNnotesLookUp.
fldRNnotesCode)="E"))
ORDER BY tblRNnotesLookUp.fldOrder;

What I want is the query output to modified based on the value in the
fldVisitType. If the valueof fldVisitType =1, then the criteria for the for
fldRNnotesCode will be (((tblRNnotesLookUp.fldRNnotesCode)="A" Or
(tblRNnotesLookUp.fldRNnotesCode)="E"))

Hope that clarifies. Thanks for your help.

How about explaining in words what you're trying to do? As you saw, your
first attempt to describe was a little to abbreviated, since what I posted
in response to the literal question apparently wasn't sufficient to meet all
your needs.

Ok Doug, here's my latest try. This goes in the criteria section in my
query

[quoted text clipped - 24 lines]

 

[Douglas J. Steele]

Since you need to map from one visit type value in the form field to
multiple possible notes codes in the table you're querying, you'd likely be
best off creating a table that maps between the two. (If it's a many-to-many
relationhips between them, you'll need to introduce an intersection table to
resolve that). You can then join that mapping table to your existing table,
and then simply compare to the value on your form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm not sure what you mean by "in the criteria section in my query".

Are you saying you've typed that into the criteria cell in the graphical
query builder?

Yes!
It's not a valid If statement (there's no End If), but even

if it were, you can't put VBA into a query like that. Additionally
"fldRNnotesCode = 'A' AND 'E'" is invalid syntax (it would likely need to
be
"fldRNnotesCode = 'A' AND fldRNnotesCode = 'E'", but that won't return
anything, since it's not possible for there to be a row which has two
different values for the same field.)

Here's the sql statement as it was before I tried to insert the If
statement
in the criteria cell of fldRNnotesCode:

SELECT tblRNnotesLookUp.fldRNnotesLUno, tblRNnotesLookUp.fldNote,
tblRNnotesLookUp.fldRNnotesCode, tblRNnotesLookUp.fldOrder
FROM tblRNnotesLookUp
WHERE (((tblRNnotesLookUp.fldRNnotesCode)="A" Or (tblRNnotesLookUp.
fldRNnotesCode)="E"))
ORDER BY tblRNnotesLookUp.fldOrder;

What I want is the query output to modified based on the value in the
fldVisitType. If the valueof fldVisitType =1, then the criteria for the
for
fldRNnotesCode will be (((tblRNnotesLookUp.fldRNnotesCode)="A" Or
(tblRNnotesLookUp.fldRNnotesCode)="E"))

Hope that clarifies. Thanks for your help.

How about explaining in words what you're trying to do? As you saw, your
first attempt to describe was a little to abbreviated, since what I posted
in response to the literal question apparently wasn't sufficient to meet
all
your needs.

Ok Doug, here's my latest try. This goes in the criteria section in my
query

[quoted text clipped - 24 lines]

I know my criteria statement is wrong, therefore your help is greatly
appreciated. Rob

 

[RobUCSD via AccessMonster.com]

Ok Doug, could you take on more crack at it. This is what I have in the
criteria cell now.

If Forms![frmPtDemographicNew]![frmVisitNewEdit].Form![ReasonForVisitTest] =
"Ablation"
WHERE=(((tblRNnotesLookUp.fldRNnotesCode)="A" Or (tblRNnotesLookUp.
fldRNnotesCode)="E"))
ORDER BY tblRNnotesLookUp.fldOrder;

I get a syntax error. I don't know how to do the transition between the If
Forms![frmPtDemographicNew]![frmVisitNewEdit].Form![ReasonForVisitTest] =
"Ablation" and the Where statement

..Could you pls help. The previous query I posted using multiple criteria
works, that's why I believe it will work if I can just pluck the visit type
of the form.

Thanks, Rob

Since you need to map from one visit type value in the form field to
multiple possible notes codes in the table you're querying, you'd likely be
best off creating a table that maps between the two. (If it's a many-to-many
relationhips between them, you'll need to introduce an intersection table to
resolve that). You can then join that mapping table to your existing table,
and then simply compare to the value on your form.

I'm not sure what you mean by "in the criteria section in my query".
[quoted text clipped - 39 lines]
I know my criteria statement is wrong, therefore your help is greatly
appreciated. Rob

 

[Douglas J. Steele]

I don't understand why you're so insistent for me to help you if you're
going to ignore my advice!

As I wrote earlier, "you can't put VBA into a query like that"

Switch to SQL view: you'll see you've created a nightmare!

I don't see why you think it'll be any easier to maintain the hard coding in
a query than it is in a form. The correct approach is to maintain the
mappings in a table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok Doug, could you take on more crack at it. This is what I have in the
criteria cell now.

If Forms![frmPtDemographicNew]![frmVisitNewEdit].Form![ReasonForVisitTest]
=
"Ablation"
WHERE=(((tblRNnotesLookUp.fldRNnotesCode)="A" Or (tblRNnotesLookUp.
fldRNnotesCode)="E"))
ORDER BY tblRNnotesLookUp.fldOrder;

I get a syntax error. I don't know how to do the transition between the If
Forms![frmPtDemographicNew]![frmVisitNewEdit].Form![ReasonForVisitTest] =
"Ablation" and the Where statement

Could you pls help. The previous query I posted using multiple criteria
works, that's why I believe it will work if I can just pluck the visit
type
of the form.

Thanks, Rob

Since you need to map from one visit type value in the form field to
multiple possible notes codes in the table you're querying, you'd likely
be
best off creating a table that maps between the two. (If it's a
many-to-many
relationhips between them, you'll need to introduce an intersection table
to
resolve that). You can then join that mapping table to your existing
table,
and then simply compare to the value on your form.

I'm not sure what you mean by "in the criteria section in my query".

[quoted text clipped - 39 lines]

I know my criteria statement is wrong, therefore your help is
greatly
appreciated. Rob

 

[RobUCSD via AccessMonster.com]

I'm sorry I have upset you. Not everybody is as keen as you are. Thanks for
your help.

I don't understand why you're so insistent for me to help you if you're
going to ignore my advice!

As I wrote earlier, "you can't put VBA into a query like that"

Switch to SQL view: you'll see you've created a nightmare!

I don't see why you think it'll be any easier to maintain the hard coding in
a query than it is in a form. The correct approach is to maintain the
mappings in a table.

Ok Doug, could you take on more crack at it. This is what I have in the
criteria cell now.

[quoted text clipped - 33 lines]