Add Date to Time value (1 Viewer)

RiskyP

Registered User.
Local time
Today, 07:04
Joined
Jan 3, 2013
Messages
27
Hello,

I have been searching all over for an answer to this, and so far found nothing.
The date variable in VBA can store date and time together. So to make my table smaller and easier to modifiy I have decided to store date and time values in the same feild, using textboxes to 'extract' the required part when needed.
I have a form connected to a table with my date/time field which will be hidden, and there are then 2 seperate boxes for Date and Time input. On load, both textboxes are populated with their respective values from the hidden textbox which works fine. But when the user modifieds either box, and clicks a button, VBA should add both time and date into the hidden connected field together, the issue is no matter what i try I keep getting miss-match errors. Have tried different, and no, formats on all/each textboxes and adding formats to VBA ect... and nothing has worked.
Code currently looks like this;
Code:
Me.Arrival1.Value = Me.arivaldate.Value + Me.arrivaltime.Value

And for reference - onload code that works fine looks like this;
Code:
Me.arivaldate.Value = Format(Me.Arrival1.Value, "dd/mm/yyyy")
Me.arritime.Value = Format(Me.Arrival1.Value, "hh:mm")

Any help would be hugly appreciated as it would save me having to add many more feilds to my table

P
 

pwbrown

Registered User.
Local time
Today, 07:04
Joined
Oct 1, 2012
Messages
170
Try this:
Format([Me.arivaldate.Value] + [Me.arritime.Value], "dd mm yyyy hh:mm")

Edit:
opps noticed you're using textboxes remove the [] from both.
 

RiskyP

Registered User.
Local time
Today, 07:04
Joined
Jan 3, 2013
Messages
27
Many thanks for your response - this has helped, however the issue, it turns out, is to do with the connection to the table, and not with the formatting. I can (using your code below) assign the value to an unbound textbox, but when I bind the textbox to the table, it does not like the format. I have tried playing around with format on the table design but that hasn't helped

What I think I will have to do is SQL to update all date fields

Thanks,
P


Try this:
Format([Me.arivaldate.Value] + [Me.arritime.Value], "dd mm yyyy hh:mm")

Edit:
opps noticed you're using textboxes remove the [] from both.
 

pr2-eugin

Super Moderator
Local time
Today, 07:04
Joined
Nov 30, 2011
Messages
8,494
Try the following..
Code:
Me.Arrival1 = CDate(Me.arivaldate & " " & Me.arrivaltime)
Also use Form_Current() rather than OnLoad.. Load is called only once in the life time of a Form.. Current is called every time you move between records..
 

Brianwarnock

Retired
Local time
Today, 07:04
Joined
Jun 2, 2003
Messages
12,701
I must be missing something here , but why not just have the user input to a bound control formatted for date and time ?

Brian
 

RiskyP

Registered User.
Local time
Today, 07:04
Joined
Jan 3, 2013
Messages
27
Many thanks - Solved!

and thanks for the tip, but using OnLoad as user will only ever load this form once each time, it will close and re-open to load new data (due to the way the user has to input)

Thanks for all your help
P

Try the following..
Code:
Me.Arrival1 = CDate(Me.arivaldate & " " & Me.arrivaltime)
Also use Form_Current() rather than OnLoad.. Load is called only once in the life time of a Form.. Current is called every time you move between records..
 

RiskyP

Registered User.
Local time
Today, 07:04
Joined
Jan 3, 2013
Messages
27
Was the plan in the first place - but I found it much easier for the users to enter two seperate fields rather than one :)

P

I must be missing something here , but why not just have the user input to a bound control formatted for date and time ?

Brian
 

Brianwarnock

Retired
Local time
Today, 07:04
Joined
Jun 2, 2003
Messages
12,701
Even with an input mask? Still you know your users best.

Brian
 

Users who are viewing this thread

Top Bottom