Text To Date Issue

[paul.clarke]

Hi

So i have all dates fields set as text fields within Acess, so its format is like yyyy-mm-dd-hh-mm.000000 etc. I can use Left(date,10) to get YYYY-MM-DD, i can live with this rather than DD-MM-YYY.

MY issue now is i need to be able to add a number (leadtime) to this date, but it doesnt like it.

How can i resolve this? I've tried all sorts of formatting the date via properties, using cdate, even left,mid,right etc to get format i like, but whatever i do, i still can not for the life of me get a made up field within a query to day date (being text) + leadtime (being a number)

pppppplease.. any suggestions?

 

[pr2-eugin]

Hmmm.. So you want the date as mm/dd/yyyy and be able to add specific amount of days to it? If that is correct, keep reading else plese give a secnario..
Lets say the date is stored inside a variable called dateStr..

dateStr="17/05/2012 13:05:06"
dateStr=Left(dateStr,10)
Dim conStr As Date
conStr=CDate(dateStr)
conStr=DateAdd("d",number_of_days,conStr)
dateStr=CStr(conStr)

Hope this helps.

 

[paul.clarke]

thanks for the reply. I can use access fairly well, but when it comes to coding i havent got a clue. wouldnt know where to start, is there a way i can do without vba? if not, where would the vba go. im using a query, not a form

 

[pr2-eugin]

If it is not much trouble, could you give the query that you have come up with so far?

 

[paul.clarke]

how can i give you a query. do u mean explain it?
basically i am selected fields from various database tables, the only calc i am doing is this datefield + a number (leadtime). all other fields are pretty much just dragged from the tables. I have used left(date,10) on some to only return yyyy-mm-dd, rather than the full yyyy-dd-mm-hh-mm.00000

is that of any use?

 

[pr2-eugin]

I understood what you are trying to do, but I need to see what you have done.. Just copy the query using SQL view at the bottom of the query window; and paste it here as a reply.

 

[paul.clarke]

SELECT Left([DockingDate],10) AS DockDate2, [Agent LeadTimes].LeadTime
FROM (scheme_poheadm INNER JOIN ([BRS master dbo import] INNER JOIN dbo_vw_landed_po ON ([BRS master dbo import].OpCo = dbo_vw_landed_po.op_comp) AND ([BRS master dbo import].OrderNo = dbo_vw_landed_po.ono)) ON scheme_poheadm.order_no = dbo_vw_landed_po.ono) INNER JOIN [Agent LeadTimes] ON [BRS master dbo import].Agent = [Agent LeadTimes].Agent;

 

[paul.clarke]

i am selecting alot more database fields than as above, but the SQL was very long, so i shorted it. The only other missing is the date calc im trying, simply dockDate+Leadtime

 

[pr2-eugin]

Try this.. might be a bit tricky to see.. so I have highlighted in red..

SELECT DateAdd("d",[Agent LeadTimes].LeadTime,CDate(Left([DockingDate],10))) AS DockDate2, [Agent LeadTimes].LeadTime
FROM (scheme_poheadm INNER JOIN ([BRS master dbo import] INNER JOIN dbo_vw_landed_po ON ([BRS master dbo import].OpCo = dbo_vw_landed_po.op_comp) AND ([BRS master dbo import].OrderNo = dbo_vw_landed_po.ono)) ON scheme_poheadm.order_no = dbo_vw_landed_po.ono) INNER JOIN [Agent LeadTimes] ON [BRS master dbo import].Agent = [Agent LeadTimes].Agent;

 

[paul.clarke]

Thanks. I copied that all into a new blank query, but it returns #error on the dockdate field.

 

[paul.clarke]

actually i think that was my fault, when i was adding fields back in. i have left it with docking date, leadtime and the dockdate2 calc.. and it works. hurr-rah
huge thanks..

 

[pr2-eugin]

You are welcome..

 

[paul.clarke]

so how can i get rid of the #error returned value when the docking date is blank. ive tried iif(IsError(dockingdate),"",(dockingdate)) but it doesnt like

 

[pr2-eugin]

Just add an IIF condition before the dateadd function.. Like..

SELECT IIF(Len([DockingDate]&"")=0,"",DateAdd("d",[Agent LeadTimes].LeadTime,CDate(Left([DockingDate],10)))) AS DockDate2, [A..............

Got it? It thus check if the field is empty first; if empty places an empty string inside DockDate2, else it does the calculation...

 

[paul.clarke]

cool, thanks. i done it a different way now. ive used mid, mid and left to get docking date as dd/mm/yyyy, if its blank it just returns //, so ive done if dockingdate = // then do "" else do dockingdate. seesm to work fine.

 

[pr2-eugin]

Well there you go.. Good on your own.. well done..

EDIT: Well the one I have placed will save you some execution time and computational space. As it ignores any calculation, whatsoever on the field if it is empty, the one you have proposed still is a solution.. However adds more computaional time and space.. at the end of the day it is all about the expense made...