|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 27th, 2007, 05:38 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Make table query with calculated date fields
I have a make table query that calculates requirement dates based on a
[StartDate]. All of the fields (80 of them) are set as Short Date in properties. But when the table is created all of the fields are text. I would like to avoid using the FORMAT command in the query and change the field properties of the table manually. Does anyone have a suggestion? Thank you in advance for your help. 
|
|
#2
November 27th, 2007, 05:53 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Make table query with calculated date fields
Share the SQL view of you make table query.
-- Duane Hookom Microsoft Access MVP "Harry" wrote: I have a make table query that calculates requirement dates based on a [StartDate]. All of the fields (80 of them) are set as Short Date in properties. But when the table is created all of the fields are text. I would like to avoid using the FORMAT command in the query and change the field properties of the table manually. Does anyone have a suggestion? Thank you in advance for your help.
|
|
#3
November 27th, 2007, 05:56 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Make table query with calculated date fields
Ok but it is pretty ugly
SELECT tblSetdown_VacAll.PWO, tblSetdown_VacAll.[Final Start], IIf(Left([DEBRIS TANK],2)="VA",IIf(Right([DEBRIS TANK],4)="GALV",GetBusinessDay([Final Start],-18),GetBusinessDay([Final Start],-5)),"") AS DebrisTankGatherDate, IIf(Left([DEBRIS TANK],2)="VA",IIf(Right([DEBRIS TANK],4)="GALV",GetBusinessDay([Final Start],-17),GetBusinessDay([Final Start],-4)),"") AS DebrisTankTackDate, IIf(Left([DEBRIS TANK],2)="VA",IIf(Right([DEBRIS TANK],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS DebrisTankWeldDate, IIf(Left([DEBRIS TANK],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-2),""),"") AS DebrisTankPaintDate, IIf(Left([TAIL GATE],2)="VA",IIf(Right([TAIL GATE],4)="GALV",GetBusinessDay([Final Start],-17),GetBusinessDay([Final Start],-4)),"") AS TailGateGatherDate, IIf(Left([TAIL GATE],2)="VA",IIf(Right([TAIL GATE],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS TailGateTackDate, IIf(Left([TAIL GATE],2)="VA",IIf(Right([TAIL GATE],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS TailGateWeldDate, IIf(Left([TAIL GATE],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-2),""),"") AS TailGatePaintDate, IIf(Left([DS WATER TANK],2)="VA",IIf(Right([DS WATER TANK],4)="GALV",GetBusinessDay([Final Start],-14),GetBusinessDay([Final Start],-1)),"") AS WaterTankDSGatherDate, IIf(Left([DS WATER TANK],2)="VA",IIf(Right([DS WATER TANK],4)="GALV",GetBusinessDay([Final Start],-13),GetBusinessDay([Final Start],0)),"") AS WaterTankDSTackDate, IIf(Left([DS WATER TANK],2)="VA",IIf(Right([DS WATER TANK],4)="GALV",GetBusinessDay([Final Start],-12),GetBusinessDay([Final Start],1)),"") AS WaterTankDSWeldDate, IIf(Left([DS WATER TANK],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],2),""),"") AS WaterTankDSPaintDate, IIf(Left([PS WATER TANK],2)="VA",IIf(Right([PS WATER TANK],4)="GALV",GetBusinessDay([Final Start],-14),GetBusinessDay([Final Start],-1)),"") AS WaterTankPSGatherDate, IIf(Left([PS WATER TANK],2)="VA",IIf(Right([PS WATER TANK],4)="GALV",GetBusinessDay([Final Start],-13),GetBusinessDay([Final Start],0)),"") AS WaterTankPSTackDate, IIf(Left([PS WATER TANK],2)="VA",IIf(Right([PS WATER TANK],4)="GALV",GetBusinessDay([Final Start],-12),GetBusinessDay([Final Start],1)),"") AS WaterTankPSWeldDate, IIf(Left([PS WATER TANK],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],2),""),"") AS WaterTankPSPaintDate, IIf(Left([CENTER WATER TANK],2)="VA",IIf(Right([CENTER WATER TANK],4)="GALV",GetBusinessDay([Final Start],-14),GetBusinessDay([Final Start],-1)),"") AS WaterTankCenterGatherDate, IIf(Left([CENTER WATER TANK],2)="VA",IIf(Right([CENTER WATER TANK],4)="GALV",GetBusinessDay([Final Start],-13),GetBusinessDay([Final Start],0)),"") AS WaterTankCenterTackDate, IIf(Left([CENTER WATER TANK],2)="VA",IIf(Right([CENTER WATER TANK],4)="GALV",GetBusinessDay([Final Start],-12),GetBusinessDay([Final Start],1)),"") AS WaterTankCenterWeldDate, IIf(Left([CENTER WATER TANK],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],2),""),"") AS WaterTankCenterPaintDate, IIf(Left([SUB-FRAME],2)="VA",IIf(Right([SUB-FRAME],4)="GALV",GetBusinessDay([Final Start],-21),GetBusinessDay([Final Start],-8)),"") AS SubFrameGatherDate, IIf(Left([SUB-FRAME],2)="VA",IIf(Right([SUB-FRAME],4)="GALV",GetBusinessDay([Final Start],-20),GetBusinessDay([Final Start],-7)),"") AS SubFrameTackDate, IIf(Left([SUB-FRAME],2)="VA",IIf(Right([SUB-FRAME],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS SubFrameWeldDate, IIf(Left([SUB-FRAME],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-5),""),"") AS SubFramePaintDate, IIf(Left([POWER FRAME],2)="VA",IIf(Right([POWER FRAME],4)="GALV",GetBusinessDay([Final Start],-27),GetBusinessDay([Final Start],-14)),"") AS PowerFrameGatherDate, IIf(Left([POWER FRAME],2)="VA",IIf(Right([POWER FRAME],4)="GALV",GetBusinessDay([Final Start],-2),GetBusinessDay([Final Start],-12)),"") AS PowerFrameTackDate, IIf(Left([POWER FRAME],2)="VA",IIf(Right([POWER FRAME],4)="GALV",GetBusinessDay([Final Start],-24),GetBusinessDay([Final Start],-11)),"") AS PowerFrameWeldDate, IIf(Left([POWER FRAME],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-10),""),"") AS PowerFramePaintDate, IIf(Left([AIR WATER SEPARATOR],2)="VA",IIf(Right([AIR WATER SEPARATOR],4)="GALV",GetBusinessDay([Final Start],-26),GetBusinessDay([Final Start],-13)),"") AS AirWaterSeperatorGatherDate, IIf(Left([AIR WATER SEPARATOR],2)="VA",IIf(Right([AIR WATER SEPARATOR],4)="GALV",GetBusinessDay([Final Start],-25),GetBusinessDay([Final Start],-12)),"") AS AirWaterSeperatorTackDate, IIf(Left([AIR WATER SEPARATOR],2)="VA",IIf(Right([AIR WATER SEPARATOR],4)="GALV",GetBusinessDay([Final Start],-24),GetBusinessDay([Final Start],-11)),"") AS AirWaterSeperatorWeldDate, IIf(Left([AIR WATER SEPARATOR],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-10),""),"") AS AirWaterSeperatorPaintDate, IIf(Left([OSMST BOOM],2)="VA",IIf(Right([OSMST BOOM],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS OSMSTBoomGatherDate, IIf(Left([OSMST BOOM],2)="VA",IIf(Right([OSMST BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS OSMSTBoomTackDate, IIf(Left([OSMST BOOM],2)="VA",IIf(Right([OSMST BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS OSMSTBoomWeldDate, IIf(Left([OSMST BOOM],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-1),""),"") AS OSMSTBoomPaintDate, IIf(Left([OSMVT BOOM],2)="VA",IIf(Right([OSMVT BOOM],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS OSMVTBoomGatherDate, IIf(Left([OSMVT BOOM],2)="VA",IIf(Right([OSMVT BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS OSMVTBoomTackDate, IIf(Left([OSMVT BOOM],2)="VA",IIf(Right([OSMVT BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS OSMVTBoomWeldDate, IIf(Left([OSMVT BOOM],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-1),""),"") AS OSMVTBoomPaintDate, IIf(Left([ISMST BOOM],2)="VA",IIf(Right([ISMST BOOM],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS ISMSTBoomGatherDate, IIf(Left([ISMST BOOM],2)="VA",IIf(Right([ISMST BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS ISMSTBoomTackDate, IIf(Left([ISMST BOOM],2)="VA",IIf(Right([ISMST BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS ISMSTBoomWeldDate, IIf(Left([ISMST BOOM],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-1),""),"") AS ISMSTBoomPaintDate, IIf(Left([ISMVT BOOM],2)="VA",IIf(Right([ISMVT BOOM],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS ISMVTBoomGatherDate, IIf(Left([ISMVT BOOM],2)="VA",IIf(Right([ISMVT BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS ISMVTBoomTackDate, IIf(Left([ISMVT BOOM],2)="VA",IIf(Right([ISMVT BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS ISMVTBoomWeldDate, IIf(Left([ISMVT BOOM],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-1),""),"") AS ISMVTBoomPaintDate, IIf(Left([BOOM ELBOW],2)="VA",IIf(Right([BOOM ELBOW],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS BoomElbowGatherDate, IIf(Left([BOOM ELBOW],2)="VA",IIf(Right([BOOM ELBOW],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS BoomElbowTackDate, IIf(Left([BOOM ELBOW],2)="VA",IIf(Right([BOOM ELBOW],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS BoomElbowWeldDate, IIf(Left([BOOM ELBOW],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-1),""),"") AS BoomElbowPaintDate, IIf(Left([BOOM HEAD],2)="VA",IIf(Right([BOOM HEAD],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS BoomHeadGatherDate, IIf(Left([BOOM HEAD],2)="VA",IIf(Right([BOOM HEAD],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS BoomHeadTackDate, IIf(Left([BOOM HEAD],2)="VA",IIf(Right([BOOM HEAD],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS BoomHeadWeldDate, IIf(Left([BOOM HEAD],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-1),""),"") AS BoomHeadPaintDate, IIf(Left([HOSE REEL],2)="VA",IIf(Right([HOSE REEL],4)="GALV",GetBusinessDay([Final Start],-20),GetBusinessDay([Final Start],-7)),"") AS HoseReelGatherDate, IIf(Left([HOSE REEL],2)="VA",IIf(Right([HOSE REEL],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS HoseReelTackDate, IIf(Left([HOSE REEL],2)="VA",IIf(Right([HOSE REEL],4)="GALV",GetBusinessDay([Final Start],-18),GetBusinessDay([Final Start],-5)),"") AS HoseReelWeldDate, IIf(Left([HOSE REEL],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-4),""),"") AS HoseReelPaintDate, IIf(Left([HOSE REEL FRAME],2)="VA",IIf(Right([HOSE REEL FRAME],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS HoseReelFrameGatherDate, IIf(Left([HOSE REEL FRAME],2)="VA",IIf(Right([HOSE REEL FRAME],4)="GALV",GetBusinessDay([Final Start],-18),GetBusinessDay([Final Start],-5)),"") AS HoseReelFrameTackDate, IIf(Left([HOSE REEL FRAME],2)="VA",IIf(Right([HOSE REEL FRAME],4)="GALV",GetBusinessDay([Final Start],-18),GetBusinessDay([Final Start],-5)),"") AS HoseReelFrameWeldDate, IIf(Left([HOSE REEL FRAME],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-4),""),"") AS HoseReelFramePaintDate, IIf(Left([HOSE REEL MOUNT],2)="VA",IIf(Right([HOSE REEL MOUNT],4)="GALV",GetBusinessDay([Final Start],-20),GetBusinessDay([Final Start],-7)),"") AS HoseReelMountGatherDate, IIf(Left([HOSE REEL MOUNT],2)="VA",IIf(Right([HOSE REEL MOUNT],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS HoseReelMountTackDate, IIf(Left([HOSE REEL MOUNT],2)="VA",IIf(Right([HOSE REEL MOUNT],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS HoseReelMountWeldDate, IIf(Left([HOSE REEL MOUNT],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-5),""),"") AS HoseReelMountPaintDate, IIf(Left([HOSE REEL BASE],2)="VA",IIf(Right([HOSE REEL BASE],4)="GALV",GetBusinessDay([Final Start],-20),GetBusinessDay([Final Start],-7)),"") AS HoseReelBaseGatherDate, IIf(Left([HOSE REEL BASE],2)="VA",IIf(Right([HOSE REEL BASE],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS HoseReelBaseTackDate, IIf(Left([HOSE REEL BASE],2)="VA",IIf(Right([HOSE REEL BASE],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS HoseReelBaseWeldDate, IIf(Left([HOSE REEL BASE],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-5),""),"") AS HoseReelBasePaintDate INTO tblWeldmentDates_SafeJet FROM tblSetdown_VacAll INNER JOIN tblMajors_SAFEJETVAC ON tblSetdown_VacAll.PWO = tblMajors_SAFEJETVAC.PWO ORDER BY tblSetdown_VacAll.[Final Start]; "Duane Hookom" wrote: Share the SQL view of you make table query. -- Duane Hookom Microsoft Access MVP "Harry" wrote: I have a make table query that calculates requirement dates based on a [StartDate]. All of the fields (80 of them) are set as Short Date in properties. But when the table is created all of the fields are text. I would like to avoid using the FORMAT command in the query and change the field properties of the table manually. Does anyone have a suggestion? Thank you in advance for your help.
|
|
#4
November 27th, 2007, 06:03 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Make table query with calculated date fields
Do not use format but use DateAdd function.
With 80 date fields it sounds like a spreadsheet instead of a relational database table structure. What about using multiple records instead of the many fields? -- KARL DEWEY Build a little - Test a little "Harry" wrote: I have a make table query that calculates requirement dates based on a [StartDate]. All of the fields (80 of them) are set as Short Date in properties. But when the table is created all of the fields are text. I would like to avoid using the FORMAT command in the query and change the field properties of the table manually. Does anyone have a suggestion? Thank you in advance for your help.
|
|
#5
November 27th, 2007, 06:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Make table query with calculated date fields
It WAS a spreadsheet. An unworkable spreadsheet. I have been tasked with
concerting to a database, you'll love this part, that looks exactly like the spreadsheet. "KARL DEWEY" wrote: Do not use format but use DateAdd function. With 80 date fields it sounds like a spreadsheet instead of a relational database table structure. What about using multiple records instead of the many fields? -- KARL DEWEY Build a little - Test a little "Harry" wrote: I have a make table query that calculates requirement dates based on a [StartDate]. All of the fields (80 of them) are set as Short Date in properties. But when the table is created all of the fields are text. I would like to avoid using the FORMAT command in the query and change the field properties of the table manually. Does anyone have a suggestion? Thank you in advance for your help.
|
|
#6
November 27th, 2007, 06:40 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Make table query with calculated date fields
Harry
Just because you can ... I can drive nails with my chainsaw, but it isn't pretty, and it isn't safe, and it doesn't take advantage of the capabilities of the tool. If you want to get the best use of Access' relationally-oriented features and functions, you'll need to brush up on "normalization" and you'll need to re-design your table structure. By the way, if the data is already available somewhere, you may not NEED to make a new table. A make-table query is a query ... could you use a SELECT query and get the same values without creating a new table? If you MUST have data in a table, consider using a DELETE query to empty an already created/defined table and an APPEND query to add in the new data. Regards Jeff Boyce Microsoft Office/Access MVP "Harry" wrote in message news  It WAS a spreadsheet. An unworkable spreadsheet. I have been tasked with concerting to a database, you'll love this part, that looks exactly like the spreadsheet. "KARL DEWEY" wrote: Do not use format but use DateAdd function. With 80 date fields it sounds like a spreadsheet instead of a relational database table structure. What about using multiple records instead of the many fields? -- KARL DEWEY Build a little - Test a little "Harry" wrote: I have a make table query that calculates requirement dates based on a [StartDate]. All of the fields (80 of them) are set as Short Date in properties. But when the table is created all of the fields are text. I would like to avoid using the FORMAT command in the query and change the field properties of the table manually. Does anyone have a suggestion? Thank you in advance for your help.
|
|
#7
November 27th, 2007, 07:36 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Make table query with calculated date fields
Instead of returning an empty string, return NULL. IF you return a string
as one of the values, then Access defines all the values as strings using implied conversion. SELECT tblSetdown_VacAll.PWO , tblSetdown_VacAll.[Final Start], IIf(Left([DEBRIS TANK],2)="VA",IIf(Right([DEBRIS TANK],4)="GALV" ,GetBusinessDay([Final Start],-18) ,GetBusinessDay([Final Start],-5)),NULL) AS DebrisTankGatherDate, IIf(Left([DEBRIS TANK],2)="VA",IIf(Right([DEBRIS TANK],4)="GALV" ,GetBusinessDay([Final Start],-17) ,GetBusinessDay([Final Start],-4)),NULL) AS DebrisTankTackDate, IIf(Left([DEBRIS TANK],2)="VA",IIf(Right([DEBRIS TANK],4)="GALV" ,GetBusinessDay([Final Start],-16) ,GetBusinessDay([Final Start],-3)),NULL) AS DebrisTankWeldDate, ,IIf(Left([DEBRIS TANK],2)="VA",IIf([Final Start]#1/1/2000# ,GetBusinessDay([Final Start],-2),Null),Null) AS DebrisTankPaintDate, You've already heard the lecture on normalizing your data structure - but sometimes, ya gotta do what ya gotta do. Hope this helps -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Harry" wrote in message ... Ok but it is pretty ugly "Duane Hookom" wrote: Share the SQL view of you make table query. -- Duane Hookom Microsoft Access MVP "Harry" wrote: I have a make table query that calculates requirement dates based on a [StartDate]. All of the fields (80 of them) are set as Short Date in properties. But when the table is created all of the fields are text. I would like to avoid using the FORMAT command in the query and change the field properties of the table manually. Does anyone have a suggestion? Thank you in advance for your help.
|
|
#8
November 27th, 2007, 07:48 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Make table query with calculated date fields
It looks like you have a mess :-( This doesn't look very normalized plus you
seem to be storing multiple data values within a single field (using Right() and Left() to extract information). The IIf() function returns a variant. If you want these to be dates then you may need to wrap them in CDate(IIf( )) You are also storing tons of "business logic" in your query. This should be done in tables or a module of business functions. I see too many values like -12, -13, -1,... -- Duane Hookom Microsoft Access MVP "Harry" wrote: Ok but it is pretty ugly SELECT tblSetdown_VacAll.PWO, tblSetdown_VacAll.[Final Start], IIf(Left([DEBRIS TANK],2)="VA",IIf(Right([DEBRIS TANK],4)="GALV",GetBusinessDay([Final Start],-18),GetBusinessDay([Final Start],-5)),"") AS DebrisTankGatherDate, IIf(Left([DEBRIS TANK],2)="VA",IIf(Right([DEBRIS TANK],4)="GALV",GetBusinessDay([Final Start],-17),GetBusinessDay([Final Start],-4)),"") AS DebrisTankTackDate, IIf(Left([DEBRIS TANK],2)="VA",IIf(Right([DEBRIS TANK],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS DebrisTankWeldDate, IIf(Left([DEBRIS TANK],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-2),""),"") AS DebrisTankPaintDate, IIf(Left([TAIL GATE],2)="VA",IIf(Right([TAIL GATE],4)="GALV",GetBusinessDay([Final Start],-17),GetBusinessDay([Final Start],-4)),"") AS TailGateGatherDate, IIf(Left([TAIL GATE],2)="VA",IIf(Right([TAIL GATE],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS TailGateTackDate, IIf(Left([TAIL GATE],2)="VA",IIf(Right([TAIL GATE],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS TailGateWeldDate, IIf(Left([TAIL GATE],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-2),""),"") AS TailGatePaintDate, IIf(Left([DS WATER TANK],2)="VA",IIf(Right([DS WATER TANK],4)="GALV",GetBusinessDay([Final Start],-14),GetBusinessDay([Final Start],-1)),"") AS WaterTankDSGatherDate, IIf(Left([DS WATER TANK],2)="VA",IIf(Right([DS WATER TANK],4)="GALV",GetBusinessDay([Final Start],-13),GetBusinessDay([Final Start],0)),"") AS WaterTankDSTackDate, IIf(Left([DS WATER TANK],2)="VA",IIf(Right([DS WATER TANK],4)="GALV",GetBusinessDay([Final Start],-12),GetBusinessDay([Final Start],1)),"") AS WaterTankDSWeldDate, IIf(Left([DS WATER TANK],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],2),""),"") AS WaterTankDSPaintDate, IIf(Left([PS WATER TANK],2)="VA",IIf(Right([PS WATER TANK],4)="GALV",GetBusinessDay([Final Start],-14),GetBusinessDay([Final Start],-1)),"") AS WaterTankPSGatherDate, IIf(Left([PS WATER TANK],2)="VA",IIf(Right([PS WATER TANK],4)="GALV",GetBusinessDay([Final Start],-13),GetBusinessDay([Final Start],0)),"") AS WaterTankPSTackDate, IIf(Left([PS WATER TANK],2)="VA",IIf(Right([PS WATER TANK],4)="GALV",GetBusinessDay([Final Start],-12),GetBusinessDay([Final Start],1)),"") AS WaterTankPSWeldDate, IIf(Left([PS WATER TANK],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],2),""),"") AS WaterTankPSPaintDate, IIf(Left([CENTER WATER TANK],2)="VA",IIf(Right([CENTER WATER TANK],4)="GALV",GetBusinessDay([Final Start],-14),GetBusinessDay([Final Start],-1)),"") AS WaterTankCenterGatherDate, IIf(Left([CENTER WATER TANK],2)="VA",IIf(Right([CENTER WATER TANK],4)="GALV",GetBusinessDay([Final Start],-13),GetBusinessDay([Final Start],0)),"") AS WaterTankCenterTackDate, IIf(Left([CENTER WATER TANK],2)="VA",IIf(Right([CENTER WATER TANK],4)="GALV",GetBusinessDay([Final Start],-12),GetBusinessDay([Final Start],1)),"") AS WaterTankCenterWeldDate, IIf(Left([CENTER WATER TANK],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],2),""),"") AS WaterTankCenterPaintDate, IIf(Left([SUB-FRAME],2)="VA",IIf(Right([SUB-FRAME],4)="GALV",GetBusinessDay([Final Start],-21),GetBusinessDay([Final Start],-8)),"") AS SubFrameGatherDate, IIf(Left([SUB-FRAME],2)="VA",IIf(Right([SUB-FRAME],4)="GALV",GetBusinessDay([Final Start],-20),GetBusinessDay([Final Start],-7)),"") AS SubFrameTackDate, IIf(Left([SUB-FRAME],2)="VA",IIf(Right([SUB-FRAME],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS SubFrameWeldDate, IIf(Left([SUB-FRAME],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-5),""),"") AS SubFramePaintDate, IIf(Left([POWER FRAME],2)="VA",IIf(Right([POWER FRAME],4)="GALV",GetBusinessDay([Final Start],-27),GetBusinessDay([Final Start],-14)),"") AS PowerFrameGatherDate, IIf(Left([POWER FRAME],2)="VA",IIf(Right([POWER FRAME],4)="GALV",GetBusinessDay([Final Start],-2),GetBusinessDay([Final Start],-12)),"") AS PowerFrameTackDate, IIf(Left([POWER FRAME],2)="VA",IIf(Right([POWER FRAME],4)="GALV",GetBusinessDay([Final Start],-24),GetBusinessDay([Final Start],-11)),"") AS PowerFrameWeldDate, IIf(Left([POWER FRAME],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-10),""),"") AS PowerFramePaintDate, IIf(Left([AIR WATER SEPARATOR],2)="VA",IIf(Right([AIR WATER SEPARATOR],4)="GALV",GetBusinessDay([Final Start],-26),GetBusinessDay([Final Start],-13)),"") AS AirWaterSeperatorGatherDate, IIf(Left([AIR WATER SEPARATOR],2)="VA",IIf(Right([AIR WATER SEPARATOR],4)="GALV",GetBusinessDay([Final Start],-25),GetBusinessDay([Final Start],-12)),"") AS AirWaterSeperatorTackDate, IIf(Left([AIR WATER SEPARATOR],2)="VA",IIf(Right([AIR WATER SEPARATOR],4)="GALV",GetBusinessDay([Final Start],-24),GetBusinessDay([Final Start],-11)),"") AS AirWaterSeperatorWeldDate, IIf(Left([AIR WATER SEPARATOR],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-10),""),"") AS AirWaterSeperatorPaintDate, IIf(Left([OSMST BOOM],2)="VA",IIf(Right([OSMST BOOM],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS OSMSTBoomGatherDate, IIf(Left([OSMST BOOM],2)="VA",IIf(Right([OSMST BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS OSMSTBoomTackDate, IIf(Left([OSMST BOOM],2)="VA",IIf(Right([OSMST BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS OSMSTBoomWeldDate, IIf(Left([OSMST BOOM],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-1),""),"") AS OSMSTBoomPaintDate, IIf(Left([OSMVT BOOM],2)="VA",IIf(Right([OSMVT BOOM],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS OSMVTBoomGatherDate, IIf(Left([OSMVT BOOM],2)="VA",IIf(Right([OSMVT BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS OSMVTBoomTackDate, IIf(Left([OSMVT BOOM],2)="VA",IIf(Right([OSMVT BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS OSMVTBoomWeldDate, IIf(Left([OSMVT BOOM],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-1),""),"") AS OSMVTBoomPaintDate, IIf(Left([ISMST BOOM],2)="VA",IIf(Right([ISMST BOOM],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS ISMSTBoomGatherDate, IIf(Left([ISMST BOOM],2)="VA",IIf(Right([ISMST BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS ISMSTBoomTackDate, IIf(Left([ISMST BOOM],2)="VA",IIf(Right([ISMST BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS ISMSTBoomWeldDate, IIf(Left([ISMST BOOM],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-1),""),"") AS ISMSTBoomPaintDate, IIf(Left([ISMVT BOOM],2)="VA",IIf(Right([ISMVT BOOM],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS ISMVTBoomGatherDate, IIf(Left([ISMVT BOOM],2)="VA",IIf(Right([ISMVT BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS ISMVTBoomTackDate, IIf(Left([ISMVT BOOM],2)="VA",IIf(Right([ISMVT BOOM],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS ISMVTBoomWeldDate, IIf(Left([ISMVT BOOM],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-1),""),"") AS ISMVTBoomPaintDate, IIf(Left([BOOM ELBOW],2)="VA",IIf(Right([BOOM ELBOW],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS BoomElbowGatherDate, IIf(Left([BOOM ELBOW],2)="VA",IIf(Right([BOOM ELBOW],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS BoomElbowTackDate, IIf(Left([BOOM ELBOW],2)="VA",IIf(Right([BOOM ELBOW],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS BoomElbowWeldDate, IIf(Left([BOOM ELBOW],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-1),""),"") AS BoomElbowPaintDate, IIf(Left([BOOM HEAD],2)="VA",IIf(Right([BOOM HEAD],4)="GALV",GetBusinessDay([Final Start],-16),GetBusinessDay([Final Start],-3)),"") AS BoomHeadGatherDate, IIf(Left([BOOM HEAD],2)="VA",IIf(Right([BOOM HEAD],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS BoomHeadTackDate, IIf(Left([BOOM HEAD],2)="VA",IIf(Right([BOOM HEAD],4)="GALV",GetBusinessDay([Final Start],-15),GetBusinessDay([Final Start],-2)),"") AS BoomHeadWeldDate, IIf(Left([BOOM HEAD],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-1),""),"") AS BoomHeadPaintDate, IIf(Left([HOSE REEL],2)="VA",IIf(Right([HOSE REEL],4)="GALV",GetBusinessDay([Final Start],-20),GetBusinessDay([Final Start],-7)),"") AS HoseReelGatherDate, IIf(Left([HOSE REEL],2)="VA",IIf(Right([HOSE REEL],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS HoseReelTackDate, IIf(Left([HOSE REEL],2)="VA",IIf(Right([HOSE REEL],4)="GALV",GetBusinessDay([Final Start],-18),GetBusinessDay([Final Start],-5)),"") AS HoseReelWeldDate, IIf(Left([HOSE REEL],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-4),""),"") AS HoseReelPaintDate, IIf(Left([HOSE REEL FRAME],2)="VA",IIf(Right([HOSE REEL FRAME],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS HoseReelFrameGatherDate, IIf(Left([HOSE REEL FRAME],2)="VA",IIf(Right([HOSE REEL FRAME],4)="GALV",GetBusinessDay([Final Start],-18),GetBusinessDay([Final Start],-5)),"") AS HoseReelFrameTackDate, IIf(Left([HOSE REEL FRAME],2)="VA",IIf(Right([HOSE REEL FRAME],4)="GALV",GetBusinessDay([Final Start],-18),GetBusinessDay([Final Start],-5)),"") AS HoseReelFrameWeldDate, IIf(Left([HOSE REEL FRAME],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-4),""),"") AS HoseReelFramePaintDate, IIf(Left([HOSE REEL MOUNT],2)="VA",IIf(Right([HOSE REEL MOUNT],4)="GALV",GetBusinessDay([Final Start],-20),GetBusinessDay([Final Start],-7)),"") AS HoseReelMountGatherDate, IIf(Left([HOSE REEL MOUNT],2)="VA",IIf(Right([HOSE REEL MOUNT],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS HoseReelMountTackDate, IIf(Left([HOSE REEL MOUNT],2)="VA",IIf(Right([HOSE REEL MOUNT],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS HoseReelMountWeldDate, IIf(Left([HOSE REEL MOUNT],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-5),""),"") AS HoseReelMountPaintDate, IIf(Left([HOSE REEL BASE],2)="VA",IIf(Right([HOSE REEL BASE],4)="GALV",GetBusinessDay([Final Start],-20),GetBusinessDay([Final Start],-7)),"") AS HoseReelBaseGatherDate, IIf(Left([HOSE REEL BASE],2)="VA",IIf(Right([HOSE REEL BASE],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS HoseReelBaseTackDate, IIf(Left([HOSE REEL BASE],2)="VA",IIf(Right([HOSE REEL BASE],4)="GALV",GetBusinessDay([Final Start],-19),GetBusinessDay([Final Start],-6)),"") AS HoseReelBaseWeldDate, IIf(Left([HOSE REEL BASE],2)="VA",IIf([Final Start]#1/1/2000#,GetBusinessDay([Final Start],-5),""),"") AS HoseReelBasePaintDate INTO tblWeldmentDates_SafeJet FROM tblSetdown_VacAll INNER JOIN tblMajors_SAFEJETVAC ON tblSetdown_VacAll.PWO = tblMajors_SAFEJETVAC.PWO ORDER BY tblSetdown_VacAll.[Final Start]; "Duane Hookom" wrote: Share the SQL view of you make table query. -- Duane Hookom Microsoft Access MVP "Harry" wrote: I have a make table query that calculates requirement dates based on a [StartDate]. All of the fields (80 of them) are set as Short Date in properties. But when the table is created all of the fields are text. I would like to avoid using the FORMAT command in the query and change the field properties of the table manually. Does anyone have a suggestion? Thank you in advance for your help.
|
|
#9
November 27th, 2007, 08:00 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Make table query with calculated date fields
and some of the iif cases seems to return a zero length string, "", rather
than a null. So it may happen that with such empty string, your data type got switched to ... string, rather than to nullable date_time. Vanderghast, Access MVP
|
|
#10
November 28th, 2007, 01:10 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Make table query with calculated date fields
Could not agree with you more. Unfortunately, I work for people who think the
two greatest achievements of man are VLOOKUP and NETDATE. I elected to use a make table query for speed. As a select query the form refreshed too slow on my computer (an engineering workstation) and most the the users are on thin clients. As to your question about if the data was available somewhere else the short answer is no but it should be. I work for a manufacturer whose business system is on Oracle, and it cannot tell us what to build or when to build it. Ironic isn't it. "Jeff Boyce" wrote: Harry Just because you can ... I can drive nails with my chainsaw, but it isn't pretty, and it isn't safe, and it doesn't take advantage of the capabilities of the tool. If you want to get the best use of Access' relationally-oriented features and functions, you'll need to brush up on "normalization" and you'll need to re-design your table structure. By the way, if the data is already available somewhere, you may not NEED to make a new table. A make-table query is a query ... could you use a SELECT query and get the same values without creating a new table? If you MUST have data in a table, consider using a DELETE query to empty an already created/defined table and an APPEND query to add in the new data. Regards Jeff Boyce Microsoft Office/Access MVP "Harry" wrote in message news It WAS a spreadsheet. An unworkable spreadsheet. I have been tasked with concerting to a database, you'll love this part, that looks exactly like the spreadsheet. "KARL DEWEY" wrote: Do not use format but use DateAdd function. With 80 date fields it sounds like a spreadsheet instead of a relational database table structure. What about using multiple records instead of the many fields? -- KARL DEWEY Build a little - Test a little "Harry" wrote: I have a make table query that calculates requirement dates based on a [StartDate]. All of the fields (80 of them) are set as Short Date in properties. But when the table is created all of the fields are text. I would like to avoid using the FORMAT command in the query and change the field properties of the table manually. Does anyone have a suggestion? Thank you in advance for your help.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
