Make table query with calculated date fields

[Harry]

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.

 

[Duane Hookom]

Share the SQL view of you make table query.

 

[Harry]

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];

Share the SQL view of you make table query.

--
Duane Hookom
Microsoft Access MVP

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.

 

[KARL DEWEY]

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?

 

[Harry]

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.

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

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.

 

[Jeff Boyce]

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

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.

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

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.

 

[John Spencer]

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
..

Ok but it is pretty ugly

Share the SQL view of you make table query.

--
Duane Hookom
Microsoft Access MVP

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.

 

[Duane Hookom]

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

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];

Share the SQL view of you make table query.

--
Duane Hookom
Microsoft Access MVP

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.

 

[Michel Walsh]

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

 

[Harry]

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.

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

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.

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


:

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.