String to DateTime

[Ruzz2k]

Hi I have a form which has a text box with date and a text box with a time. DD/MM/YYYY AND HH:NN:SS

In vba I have declared A as a date and done the following

A=Format([Textbox1] & " " & [Textbox2],"dd/mm/yyyy hh:nn:ss AM/PM")
msgbox (A)

msgbox shows everything but the AM/PM

Now I need need to pass this date to excel to do a vlookup which works if I do the following

A=#03/05/2013 11:26:00 AM#

but it wont work if I grab the date and time from the access form, I think it is because the AM/PM is missing. Please can someone help me I can post the full code if you like.

 

[Dlovan-IT]

if you meant ,,, try it

Dim d As Date
If d = "03/05/2013 11:26:00 AM" Then

anything
Else
d = Now
MsgBox (d)
End If

 

[Ruzz2k]

sorry what do you mean if I meant ,,,?

my problem is the date doesn't format with AM/PM

thanks

 

[Dlovan-IT]

sorry i don't understand completely , can you tell me shortly ! what date ?

 

[Ruzz2k]

ok in my first post Textbox1 is 03/05/2013 and Textbox2 is 11:26:00
Now I am trying to make that into 03/05/2013 11:26:00 AM but it does not happen all I get is 03/05/2013 11:26:00 .

Now I need to use this date to do a vlookup when I use #03/05/2012 11:26:00 AM# as my lookup it working but I need it to lookup using the text boxes on the form as the date and time can change. But it does not work when I try to add the two strings together as it does not give me the AM part of the date.

 

[CJ_London]

Try converting it to a proper date type first:

DateValue(TextBox1)+Datevalue(Textbox2)

 

[MarkK]

In VBA a date is stored as a number, and the Format function returns a string. If you assign the result of Format() to a date variable, the formatting is lost because the "Formatted" string is converted back to a date type. If you want to communicate with excel, communicate the date variable, and do the formatting, which should always be your last step anyway, in Excel.

 

[namliam]

What lagbolt said, date is but a value and the AM/PM thing is but a formatting issue...

Try filling in 2 PM and you will see the time is displayed as 14:00, i.e. 24 hour clock. Stick a formatting on the cell and you should be golden.

 

[Ruzz2k]

Hi Namilam I am not sure what you are asking me to check but please see my code. If I pass the c into the vlookup it will show the date as 03/05/2013 11:26:00 AM but does not do the lookup. If I pass d into the vlookup it does the lookup correctly. VBA formats 03/05/2013 11:26:00 into 3/5/2013 11:26:00 AM. I know im close but i have been going round in circles. Thanks please tell me what to change and I will test it. Thanks.

Private Sub Command84_Click()
Dim objExcel As Excel.Application
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number = 429 Then 'Excel not running - start it
    Set objExcel = CreateObject("Excel.Application")
    Err.Clear
End If
Dim result As Variant
Dim a As Date
Dim b As String
Dim c As Variant
Dim d As Date
a = DateValue([Forms]![frmCalibration]![Calibration Date])
b = Format([Forms]![frmCalibration]![RD Time1], "hh:nn:ss AMPM")
c = Format(a + b, "dd/mm/yyyy hh:nn:ss AM/PM")
'd = #3/5/2013 11:26:00 AM#
MsgBox (a)
MsgBox (b)
MsgBox (c)
objExcel.Workbooks.Open "C:\Test.csv", True, False
objExcel.Visible = False
Sheets("Test").Activate
Dim myrange As Range
Set myrange = Range("A1:C100")
result = objExcel.Application.VLookup(CDbl(c), myrange, 3, False)
MsgBox result

ActiveWorkbook.Close SaveChanges:=False
objExcel.Application.Quit

Set objExcel = Nothing
End Sub

 

[namliam]

HUGE difference between c and d, though they look the same they are RADICALY different.
Format will yield a STRING value, which is stored in c. d is actually an actual date...

this is what I am trying to say, AM/PM is NOT part of an actual date... it is actually a formatting/display thingy to make human sence of things. Even the date or time is only a format/display thing.... even a cell containing your full date/time 3/5/2013 11:26:00 AM, can display (only) March with a small formatting. Doesnt mean it actually says March, it is that actual date and if you go search you will never find March because it doesnt exist except for our visual perception...

All in all though, I dont understand why so many people now a days use this excel.VLookup nonsence. Simply import your excel sheet or use it as a linked table and query it, instead of doing this long way around VLookup stuff

 

[Ruzz2k]

I was Trying to.avoid adding 3 tables.per.calibration being added.into.access.multiplied.by 1400
I fear.that the.database may corrup. With.that many.temporary tables.being adde. And.deleted
Note.my.date.is.in the.UK.format date.month year.is.that an issue?
I'm.not.sure where.I.will.go.with this.now
Thanks.for.your.help

 

[namliam]

:banghead: Is your space bar broken? :banghead:

I am not saying you should add and remove tables, I mean you can have 3 linked tables to excel sheets and relink them depending on which one you actually need.
Perhaps even you can store "all" that data in one table, I dont know what your design is about... Just that using a VLookup is like erm... putting a lawnmower engine into a car... it works but yeeeeeaaaaaaahhhhhhhhh

which ever format your date is in, UK, NL, US, IT, ISO, Marsian or Vulcan, it DOES NOT MATTER... a date is a date, a string is a string...
If you try finding a date with a string, it wont work

Its like comparing apples and oranges, you cant... you must compare apples with apples... Dates with Dates and Strings with Strings (and numbers with numbers)... mix the types and you are in trouble.

 

[Ruzz2k]

Hi . Totally get date.is.just a double value and string and date is.different and therefore.I.can't.do.the.lookup.but . Have tried CVDate, Cdate, Dateserial,timeserial,yet my date.as.a.string remains.as.a.string.I declare.the final value as.a.date.also.tried.variant.but.still.it remains.a.string. Is.there.any.way to.do.this.
In terms on your.suggestion . I.am.looking at around 4200 files in a year. Each file.will.have 20 lookup values.
This is why.I thought linked files.or adding the data to.a.table.would.be.impractical. This.is.just.to.import.values.onto the.form you.would.otherwise.have.to.type.into.the.form.
I really do.appreciate.all.your.help.
The.thing . Guess.I.would.like to.know.is.that is.it.possible.to.take.my.string.date.and.change.it.to.an.actual.date as.if.it.is.not.then.I.will.have.to.try.something.different thanks

 

[namliam]

Instead of doing the hard vlookup and opening the excel file... simply linking the csv and querying is so much more easy.

VLookup is strong but cant really distinguish between value types well.... and date/time values dont let themselves be captured into cdbl perfectly either
Even if a date (converted to a number) shows as 41338.47916666667, searching for this exact number will not find it because there is a (teny tiny) rounding difference causing an issue. I never really found a way round it. Exact values like 12:00:00 and 6:00:00 and 18:00:00.
But broken times having multiple decimals are somehow, somewhere rounded and break in VLookups unless you use a proper time as a proper search.

Having said that, you can try using the (excel) Datevalue (or Date) and Timevalue (or Time) to convert the seperated fields and add them together