New formula changes query fields???

[pgmr]

Hello,
I have a query pulling in fields from As400 database thru ODBC which is correct.
I link an access table to above query and dates are also correct.
I add a field:

PhotoETA1:IIf((IsNull([WHP0110 Summary]![Max Of KRHDPS])),[TGT ETA (S/S)],[WHP0110 Summary]![Max Of KRHDPS])

and my field [Max of KRHDPS] becomes this PhotoETA1 date.
Any ideas would be great
Thanks

 

[dhookom]

You didn't ask a question that I can find? Are you always returning [Max Of KRHDPS] when you think you might have null values in [Max Of KRHDPS]? The Nz() function can usually replace most of your expression:
PhotoETA1:Nz([Max Of KRHDPS],[TGT ETA (S/S)])
I expect that [Max Of KRHDPS] might not be null.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pgmr]

Thanks for responding,
I am returning [TGT ETA (s/s)] when [Max of KRHDPS] is null
else [Max of KRHDPS]. I also have the field [Max of KRHDPS] in my query. My problem is when PhotoETA1 field
is added to my query, the field [Max of KRHDPS] changes too and is the same date as the PhotoETA1 field.

 

[dhookom]

From your statements, it looks like you have a field named PhotoETA1 and you are creating a new calculated column with the same name. This doesn't work. If this is the case, why would you want to do this?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pgmr]

dhookom,
I have a field PhotoETA1 which uses Max of KRHDPS in its calculation. I also have [Max of KRHDPS] as a field in my query. If I remove PhotoETA1 from the query I get the correct date, when I add it back, it messes up Max of KRHDPS. The PhotoETA1 date is doing what is expected.
We are stumped as to why the PhotoETA1 date would affect the Max of KRHDPS field at all.
I really appreciate your help.
Thanks

 

[dhookom]

Is this a totals query? What do you mean by "messes up Max of KRHDPS"? Maybe you should paste your sql view in a reply. It would help to see a sample of your results before and after adding PhotoETA1.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pgmr]

Hi,
Here is SQL before adding field PhotoETA1:

SELECT [New Table].[TGT ETA (S/S)], [New Table].[Style Number], [New Table].Color, [New Table].Country, [New Table]![I70DOMINT] AS [DOM/INT], [New Table].Factory, [New Table].[Style Description], [New Table]![PSizes] AS [Photo sizes], [New Table].[Photo Qty], [New Table].[Date Photo due\TGT X-Date], [New Table].[Date Photo sent], [New Table].[Photo ETA], [New Table].[Date Photo rec'd], [New Table].Quantity, [New Table].[Number of Photo Rec'd], [New Table].[Expected Lot Completion Date S/S TGT X-Date], [New Table].[Actual Date Sent S/S], [New Table].[HAWB#], [WHP0110 Summary].[Max Of KRHDRC], [New Table].[Rec'd @ Office], [New Table]![Sizes] AS Sizes, [New Table].[Quantity Rec'd], [New Table]![I71CUT#] AS [PO/Cut#], [New Table].[Design Approved?], [WHP0110 Summary].[Max Of KRHDPS], [New Table].[Quantity Complete?], [New Table].Season, [New Table].Comments, [WHP0110 Summary].[Min Of KRHDPS]
FROM [New Table] LEFT JOIN [WHP0110 Summary] ON ([New Table].[I71CUT#] = [WHP0110 Summary].KRHCUT) AND ([New Table].Season = [WHP0110 Summary].WHSEAYR)
WHERE ((([New Table].Season)=[Enter Season]))
ORDER BY [New Table].[TGT ETA (S/S)], [New Table].[Style Number];
And a sample of my data:
Style# TGT ETA (S/S) Max of KRHDPS
E1234 11/10/2003 11/18/2003
J12344 11/18/2003 11/18/2003
J12348 11/18/2003 11/18/2003

Query with new field PhotoETA1 added:
SELECT [New Table].[TGT ETA (S/S)], [New Table].[Style Number], [New Table].Color, [New Table].Country, [New Table]![I70DOMINT] AS [DOM/INT], [New Table].Factory, [New Table].[Style Description], [New Table]![PSizes] AS [Photo sizes], [New Table].[Photo Qty], [New Table].[Date Photo due\TGT X-Date], [New Table].[Date Photo sent], [New Table].[Photo ETA], [New Table].[Date Photo rec'd], [New Table].Quantity, [New Table].[Number of Photo Rec'd], [New Table].[Expected Lot Completion Date S/S TGT X-Date], [New Table].[Actual Date Sent S/S], [New Table].[HAWB#], [WHP0110 Summary].[Max Of KRHDRC], [New Table].[Rec'd @ Office], [New Table]![Sizes] AS Sizes, [New Table].[Quantity Rec'd], [New Table]![I71CUT#] AS [PO/Cut#], [New Table].[Design Approved?], [WHP0110 Summary].[Max Of KRHDPS], [New Table].[Quantity Complete?], [New Table].Season, [New Table].Comments, [WHP0110 Summary].[Min Of KRHDPS], IIf((IsNull([WHP0110 Summary]![Max Of KRHDPS])),[TGT ETA (S/S)],[WHP0110 Summary]![Max Of KRHDPS]) AS [Photo ETA1]
FROM [New Table] LEFT JOIN [WHP0110 Summary] ON ([New Table].[I71CUT#] = [WHP0110 Summary].KRHCUT) AND ([New Table].Season = [WHP0110 Summary].WHSEAYR)
WHERE ((([New Table].Season)=[Enter Season]))
ORDER BY [New Table].[TGT ETA (S/S)], [New Table].[Style Number];

Sample of data with PhotoETA1 date added:

Style# TGT ETA (S/S) Max of KRHDPS Photo ETA1
E1234 11/7/2003 11/4/2003 11/4/2003
J12344 11/7/2003 11/5/2003 11/5/2003
J12348 11/4/2003 11/5/2003 11/5/2003

I didn't expect Max of KRHDPS to change.
WHP0110 Summary query summarizes AS400 data (ODBC)

If you need more info let me know
Thanks, again for taking the time
Colleen

 

[dhookom]

This is quite unexpected. You should be able to use Nz() rather than your IsNull... syntax.
Nz([WHP0110 Summary]![Max Of KRHDPS],[TGT ETA (S/S)]) AS [Photo ETA1]
I haven't seen quite so many spaces and punctuation in field and table names but this shouldn't bother the SQL since you have []s around object names. Have you tried pulling all the information into Access tables and then use the same SQL?





Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pgmr]

dhookom,
I pulled the info into an Access table and used the same SQL, all the fields are correct. Thank You.
I am not sure how to update this table to get current info from the AS400. Any ideas?
Thanks, again

 

[dhookom]

I think "how to update this table to get current info from the AS400" sounds like a new thread. You would need to describe if information is updated or appended, how often, data structures,...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.