If __ Then __ type commands in queries?

[TUPJK]

I have 2 forms, one nested within another

One is an ID form, the other is drug injection order for that ID model

So say model #1, #2, #3, #4, #5, #6 all receive 20 various drugs

But only #2-6 receive a drug "GTN"



I want to run a query that will search for all models that received a 3mg dose of GTN, but also return EVERY other drug injection order that model received

So sort of something like [If (model #) receives 3mg of GTN THEN return all records for (model #)]

type command

anybody able to help?

[June7]

Construct a field in query with DLookup then filter on that field.
DLookup("fieldname","tablename", "Dose='3mg' And ModelNo='" & [ModelNo] & "'")

Filter would be: Not Is Null

In this example, 3mg is a literal string and static. Make this dynamic by referring to a control (combobox with LimitToList) on form where user enters dose value to search on.
DLookup("fieldname","tablename", "Dose='" & Forms!formname.controlname & "' And ModelNo='" & [ModelNo] & "'")

[Bob Fitz]

Hi TUPKJ,

I want to run a query that will search for all models that received a 3mg dose of GTN, but also return EVERY other drug injection order that model received

So sort of something like [If (model #) receives 3mg of GTN THEN return all records for (model #)]

I think that what you need is a query that queries a query, if that makes any sense to you.
Create a query that returns all models that received a 3mg dose of GTN. Save this query. Create another query based on the first query joined to the table used in the first query.
If this gives the result you require then this could be done using a single query and a subquery written in its' SQL.

[TUPJK]

Construct a field in query with DLookup then filter on that field.
DLookup("fieldname","tablename", "Dose='3mg' And ModelNo='" & [ModelNo] & "'")

Filter would be: Not Is Null

In this example, 3mg is a literal string and static. Make this dynamic by referring to a control (combobox with LimitToList) on form where user enters dose value to search on.
DLookup("fieldname","tablename", "Dose='" & Forms!formname.controlname & "' And ModelNo='" & [ModelNo] & "'")

I am a complete novice... what does Dlookup mean?

is this in SQL view?

[June7]

DLookup is a data aggregate function. There is a whole family of these - DSum, DMin, DMax, DAvg, DFirst, DLast.

Google it or try the Access Help for info.

[TUPJK]

OK I'm just going to use Bob Fitz's solution and just use a "make table" query, then run a query on that... seems easier

Writing code is way over my head, I don't even know how to get to the window to start writing it

[June7]

I wasn't suggesting code. The DLookup would be an expression in the query. Enter it in the Field row of the designer grid. This will result in a constructed field. This field can be treated like any of the other fields that come from the table.

[TUPJK]

I need to watch some youtube videos on how to do this

in the mean time, I will just be doing queries of queries