Make Query Conditional using a Text Field on my Form

[cw via AccessMonster.com]

I have a Form that prompts for selected information and an OK button that
opens this query:

SELECT tblCPVDSL.ID, tblCPVDSL.DslID, tblCPVDSL.ActiveID, tblCPVDSL.
ResoldVoiceTypeID, tblCPVDSL.Acct, tblCPVDSL.VpVciDlci, tblCPVDSL.
CustomerName, tblCPVDSL.Phone, tblCPVDSL.CKTID, tblCPVDSL.LATA, tblCPVDSL.
InstallDate, tblCPVDSL.Order, tblCPVDSL.CWCPtypeID, tblCPVDSL.dtmAdded,
tblCPVDSL.strModifiedBy, tblCPVDSL.dtmModified, tblCPVDSL.Notes, tblCPVDSL.
SARDate, tblCPVDSL.SARTime, tblCwCpTypes.Type, tblCwCpTypes.Description
FROM tblCPVDSL INNER JOIN tblCwCpTypes ON tblCPVDSL.CWCPtypeID = tblCwCpTypes.
CWCPtypeID
WHERE (((tblCPVDSL.CustomerName) Is Null) AND ((tblCwCpTypes.Type)="VAD"));

I have added a Text Box to my form called LATA and its values will always be
362 or 978.

How do I make my query reference this field on my form and change the WHERE
clause?

1) If the Value = 362 then (tblCwCpTypes.Type)="VAD")
or
2) If the Value = 362 then (tblCwCpTypes.Type)="VFD")

Thanks for any help or ideas,
cw

 

[pietlinden]

How do I make my query reference this field on my form and change the WHERE
clause?

1) If the Value = 362 then (tblCwCpTypes.Type)="VAD")
or
2) If the Value = 362 then (tblCwCpTypes.Type)="VFD")

Thanks for any help or ideas,
cw

Maybe I'm missing something or you mistyped something...

but the way to show one thing (e.g. "VAD") and query for another (362)
is to create a combobox with 2 columns and to hide the one containing
the value. then point your query at the form...

SELECT...
FROM...
WHERE tblCwCPTypes.Type=Forms!MyFormName!cboCwCpType;

 

[cw via AccessMonster.com]

Thanks for the reply! That did the trick.

I added another Text Box to my Form called TypeValue which will be "VAD" or
"VFD"
using an IIF statement.
Then I added the following code as you mentioned:

WHERE (((tblCPVDSL.CustomerName) Is Null) AND ((tblCwCpTypes.Type)=[Forms]!
[frmCPtypeAndLataRS]![TypeValue]));

Thanks again,
cw

How do I make my query reference this field on my form and change the WHERE
clause?

[quoted text clipped - 5 lines]

Thanks for any help or ideas,
cw

Maybe I'm missing something or you mistyped something...

but the way to show one thing (e.g. "VAD") and query for another (362)
is to create a combobox with 2 columns and to hide the one containing
the value. then point your query at the form...

SELECT...
FROM...
WHERE tblCwCPTypes.Type=Forms!MyFormName!cboCwCpType;