I have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!
Open Query if field is true
- Thread starter jamphan
- Start date
- Local time
- Today, 02:43
- Joined
- Feb 28, 2001
- Messages
- 30,499
If the three different fields are not THAT different or if they can be "cast" into similar formats, try a UNION query.
SELECT Acct, CStr$(fielda), CStr$(fieldb), CStr$(fieldc) FROM table1 WHERE ..... UNION
SELECT Acct, Cstr$(fieldd), CStr$(fielde), CStr$(fieldf) FROM table 2 WHERE .... UNION
SELECT etc.etc.etc.
Then a DCount of the query for Acct = (whatever value) tells you whether you have 0, 1, or many entries. The MANY case would occur if the Acct count appear in more than one query because the criteria for the queries overlap at some point.
SELECT Acct, CStr$(fielda), CStr$(fieldb), CStr$(fieldc) FROM table1 WHERE ..... UNION
SELECT Acct, Cstr$(fieldd), CStr$(fielde), CStr$(fieldf) FROM table 2 WHERE .... UNION
SELECT etc.etc.etc.
Then a DCount of the query for Acct = (whatever value) tells you whether you have 0, 1, or many entries. The MANY case would occur if the Acct count appear in more than one query because the criteria for the queries overlap at some point.
that seems like it would join the queries together. They need to be completely separate. The following is some code I have been trying and it is almost what i need except I believe my syntax is off somewhere. Any ideas?
Private Sub Command2_Click()
If IsNull(DLookup("[Acct]", "qryTest", "[Acct]= '& Forms![Form]![Account]'")) = True Then
If IsNull(DLookup("[Acct]", "qryTest2", "[Acct]=' & Forms![Form]![Account]'")) = True Then
DoCmd.OpenQuery "qryTest", acNormal
Else
DoCmd.OpenQuery "qryTest2", acNormal
End If
Else
MsgBox "The Account Number was not found"
End If
End Sub
Private Sub Command2_Click()
If IsNull(DLookup("[Acct]", "qryTest", "[Acct]= '& Forms![Form]![Account]'")) = True Then
If IsNull(DLookup("[Acct]", "qryTest2", "[Acct]=' & Forms![Form]![Account]'")) = True Then
DoCmd.OpenQuery "qryTest", acNormal
Else
DoCmd.OpenQuery "qryTest2", acNormal
End If
Else
MsgBox "The Account Number was not found"
End If
End Sub
- Local time
- Today, 02:43
- Joined
- Feb 28, 2001
- Messages
- 30,499
Perhaps you misunderstand. These are three queries that can exist separately if you wish, but you can cut-and-paste some parts of them for your problem. I know they are three queries - but they aren't INDEPENDENT queries if you want to merge the results for a single form.
By the way, if they three queries based on the same table, they are NOT three separate queries anyway. And if this [Acct] field is used the same way in three different tables, you might also have a normalization issue. (Though the latter is a thought or a guess, not an accusation.)
By the way, if they three queries based on the same table, they are NOT three separate queries anyway. And if this [Acct] field is used the same way in three different tables, you might also have a normalization issue. (Though the latter is a thought or a guess, not an accusation.)
