sorting by dates in access

[Schmals]

Hi!
I am running a report sorting by date. When there is no date available, the field is left blank. When i run the report, the info that has dates appears, but the ones that are left blank do not. How can i make those fields blank appear in the report...

thanks
schmals

 

[jebry]

Hi!

It sounds like the report is using a query that limits the date field. If that is the case, then add Or IsNull(YourDatefield) = True to the criteria.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[KenReay]

Hi

The dates which are "Blank" are presumably null, and are therefore excluded from the result set, either change your criteria to included Null (IsNull()) and/or see Nz() function in Help

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Schmals]

Ok,
I tried

Or IsNull(ETA USA) = True as well as
(IsNull(ETA USA))

(fyi...my column is called ETA USA)

i think it might be a tuesday morning thing... but neither one is working..i tried both ways in the criteria section of my query..
thanks in advance for all the help...

schmals

 

[jebry]

Hi schmals!

You need to put your column name in [] because of the space.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Schmals]

i tried using [] but when i did that, i got the error message:

This expression is typed incorrectly, or it is too complex to be evaluated. Try simplifying the expression by assigning parts fo the expression to variables....

anything else i could try??

thanks
schmals

 

[PHV]

Any chance you could post your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Schmals]

SELECT [Product Detail tbl].[Shipment Number] AS [Product Detail tbl_Shipment Number], [Product Detail tbl].Brand, [Product Detail tbl].[Number of Cases], [Product Detail tbl].Species, [Product Detail tbl].Form, [Product Detail tbl].Pack, [Product Detail tbl].Size, [Product Detail tbl].Cost, [Shipment Detail tbl].[Container Number], [Shipment Detail tbl].Vendor1, [Shipment Detail tbl].[Estimated Shipping Date], [Shipment Detail tbl].[Actual Shipping Date], [Shipment Detail tbl].[Shipping Line], [Shipment Detail tbl].[ETA USA], [Shipment Detail tbl].[ETA Freezer], [Shipment Detail tbl].INV
FROM [Shipment Detail tbl] INNER JOIN [Product Detail tbl] ON [Shipment Detail tbl].[Shipment Number] = [Product Detail tbl].[Shipment Number]
WHERE ((([Shipment Detail tbl].[Estimated Shipping Date]) Between [Estimated Shipping Date Beginning:] And [Estimated Shipment Date Ending:]));




the column that i need help is ETA USA. thanks again for all your help!!!!

schmals

 

[PHV]

My guess is to replace this:
WHERE ((([Shipment Detail tbl].[Estimated Shipping Date]) Between [Estimated Shipping Date Beginning:] And [Estimated Shipment Date Ending:]));
By this:
WHERE ([Shipment Detail tbl].[Estimated Shipping Date] Between [Estimated Shipping Date Beginning:] And [Estimated Shipment Date Ending:])
OR [Shipment Detail tbl].[Estimated Shipping Date] Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Schmals]

Yeah!
that worked!!!! but here is one more question for a different report... the same problem is occuring in a number column. if the guys don't have sales for an account, that account isn't showing up on the report, it is just skipping it.. i think it is the same problem, but i am not sure where to fix it.. the columns that are sometimes zeros are ar_weight and ar_sales.

here is the sql code.....

SELECT ARHIMAST.AR_MM, ARHIMAST.AR_YY, ARHITRAN.AR_WEIGHT, ARHITRAN.AR_SALES, ARHITRAN.AR_PROD, INMAST.IN_PACK, INMAST.IN_DESC, INMAST.IN_BRAND, INMAST.IN_ORIGIN, [ar_sales]-[complete cost] AS [total profit], [ar_weight]*([ar_mcost]+[ar_freunit]+[ar_rebunit]+[ar_comunit]) AS [complete cost], ARHITRAN.AR_DOCNO, ARMAST.AR_ADDR1, ARMAST.AR_ADDR2, ARMAST.AR_ADDR3, ARMAST.AR_CITY, ARMAST.AR_STATE, ARMAST.AR_ZIP, ARMAST.AR_BUYGRP, ARMAST.AR_SLSMAN, ARMAST.AR_TERMS, ARMAST.AR_PPHONE, ARMAST.AR_PFAX, ARMAST.AR_CREDIT, ARMAST.AR_PCONT1, ARMAST.AR_PCONT2, ARMAST.AR_DPHONE, ARMAST.AR_NAME, ARMAST.AR_CUST
FROM ((INMAST INNER JOIN ARHITRAN ON INMAST.IN_NO = ARHITRAN.AR_PROD) INNER JOIN ARHIMAST ON (ARHITRAN.AR_DOCNO = ARHIMAST.AR_DOCNO) AND (ARHITRAN.AR_CUST = ARHIMAST.AR_CUST)) INNER JOIN ARMAST ON ARHIMAST.AR_CUST = ARMAST.AR_CUST
WHERE (((ARHIMAST.AR_MM)="07") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="07") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="07") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="08") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="08") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="08") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="09") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="09") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="09") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="10") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="10") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="10") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="11") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="11") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="11") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="12") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="12") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="12") AND ((ARHIMAST.AR_YY)="04") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="01") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="01") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="01") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="02") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="02") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="02") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="03") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="03") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="03") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="04") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="04") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="04") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 320 And 999)) OR (((ARHIMAST.AR_MM)="05") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 1 And 313)) OR (((ARHIMAST.AR_MM)="05") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 315 And 318)) OR (((ARHIMAST.AR_MM)="05") AND ((ARHIMAST.AR_YY)="05") AND ((ARHITRAN.AR_PROD) Between 320 And 999));


thanks sooooo much for all your help...
schmals