Can this be dun? (1 Viewer)

Sm0ke · Apr 18, 2018

Hi
I am new to Access and hopping someone can help me. I have a query called FreezerSAT1 that we put the date (TDate) we do a test and what equipment ID (TEquip) we used, in another query I have all the equipment ID (InstrumentID) the date (Startdate) the equipment was calibrated and the date(DueDate) when it run out and the correction factor(CT-10). I am trying to make a query that will find the correction factor for the test equipment on the date the test was run. I am not having any luck I am trying to do it with “IIF and” IIF([TEquip]=[InstrumentID] and [TDate]>= [Startdate] and [TDate]<[DueDate],[CT-10],””)

jdraw · Apr 18, 2018

Probably, BUT you will have to provide more info. Readers only know what you tell us, and you haven't given much. What is the business --start at 30,000 feet and gradually add some details to show where Equip, Freezer etc fit into the bigger picture. People will help you but we need some context to understand your post.

Sm0ke · Apr 18, 2018

I am very new to access, I think I just attach the Data base I am working on.

Pat Hartman · Apr 18, 2018

There is nothing obvious wrong with the IIf(). To post the SQL string, open the query in design view and switch to SQL View. Then copy and paste the SQL so we can see it.

"I'm not having any luck" isn't a description that is helpful. Are you getting an error message? Does the query run? Does it produce incorrect results? Have you examined each column in the query to ensure that the record is actually what you think it is?

So to solve the problem, we need to see:
1. Data
2. SQL
3. What you get
4. What you want

Sm0ke · Apr 18, 2018

It looks like I can attach the Data Base.
when I run the IIF I get #Error
I have Attach two snipping. Data Fluke and What i need. when we put in a test date in (What I need) I need it to go to Data Fluke find the startdate and enddate the tdate fall brtween and the TEquip maches and put the value from CF-10 in this case it should be -0.20

I just Coppy it
Expr1: IIf([TEquip]=[K Type Fluke Query]![InstrumentID] And [TDate]>=[K Type Fluke Query]![Startdate] And [TDate]<[K Type Fluke Query]![DueDate],[K Type Fluke Query]![CF-10],"")

I appreciate the help and I am sorry if I cannot answer your question I am very new at this and self-school

arnelgp · Apr 18, 2018

what you need is a subquery (or dlookup, or dmax).
this is the subquery:

Expr1: (select top 1 T1.[cf-10] from [K Type Fluke Query] As T1 Where (T1.[InstrumentID]=FreezerSAT1.TEquip) And (FreezerSAT1.TDate Between T1.StartDate And T1.DueDate-1))

Sm0ke · Apr 20, 2018

I tried the Dlookup but gut #Error.

Expr1: DLookUp("cf-10","K Type Fluke Query",("InstrumentID=" & [TEquip]) And ([FreezerSAT1].[TDate] Between [StartDate] And [DueDate]-1))

arnelgp · Apr 20, 2018

Try adding [ ] to your fields:

Expr1: DLookUp("[cf-10]","[K Type Fluke Query]","(InstrumentID=[TEquip] ) And ([FreezerSAT1].[TDate] Between [StartDate] And [DueDate]-1)")

Sm0ke · Apr 20, 2018

I copy what you sent and I am now getting: “The expression you entered as a query parameter produced this error: ’Microsoft Access cannot find the name ”TEquip” you entered in the Expression’

arnelgp · Apr 20, 2018

Expr1: DLookUp("[cf-10]","[K Type Fluke Query]","(InstrumentID=" & [TEquip] & " ) And (" & [FreezerSAT1].[TDate] & " Between [StartDate] And [DueDate]-1)")

Sm0ke · Apr 20, 2018

If I change it a little the first cell is right but the rest have #Error
Expr1: Val(DLookUp("[cf-10]","[K Type Fluke Query]",("InstrumentID=" & [TEquip]) And ([FreezerSAT1].[TDate] Between [StartDate] And [DueDate]-1)))

Sm0ke · Apr 20, 2018

The last one run without Error but the cell are empty

arnelgp · Apr 20, 2018

try formatting the tdate

Expr1: DLookUp("[cf-10]","[K Type Fluke Query]","(InstrumentID=" & [TEquip] & " ) And (#" & Format([FreezerSAT1].[TDate], "mm/dd/yyyy") & "# Between [StartDate] And [DueDate]-1)")

Sm0ke · Apr 20, 2018

same s the last one, run without Error but the cell are empty

Pat Hartman · Apr 20, 2018

The DLookup() takes three STRINGS. The column you want returned, the table or query name And a where clause. You did not build the where string correctly.

Expr1: Val(DLookUp("[cf-10]","[K Type Fluke Query]",("InstrumentID=" & [TEquip]) And ([FreezerSAT1].[TDate] Between [StartDate] And [DueDate]-1)))

Should be:

Expr1: DLookUp("[cf-10]","[K Type Fluke Query]", "InstrumentID=" & [TEquip] & " And [FreezerSAT1].[TDate] Between " & [StartDate] & " And " & [DueDate]-1)

PS, you still didn't post what I asked for and that means that I have no idea Where TEquip, StartDate, and DueDate are coming from. You keep showing snippets of code out of context. If those three variables come from a form, then you need to reference them as:

Forms!yourformname!yourcontrolname

arnelgp · Apr 20, 2018

Can you remove this if this will make a diff:

[FreezerSAT1].

Sm0ke · Apr 20, 2018

same, run without Error but the cell are empty

arnelgp · Apr 20, 2018

i see that instrumentid is a string

Expr1: DLookUp("[cf-10]","[K Type Fluke Query]","(InstrumentID='" & [TEquip] & "' ) And (#" & Format([TDate], "mm/dd/yyyy") & "# Between [StartDate] And [DueDate]-1)")

Sm0ke · Apr 20, 2018

The "TEquip", "StartDate" are in a Table that go in to a Query where it add 30days to the startDate to make the "DueDate" I am showing you all the code I am putting in, if there is something missing I do know what it is. I am just copying and past, what is there I am new at this.

Sm0ke · Apr 20, 2018

That gut me the #Error

Can this be dun? (1 Viewer)

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Super Moderator

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Similar threads

Users who are viewing this thread