Easy Way (For Users) to select Date and Time on Form (1 Viewer)

[padlocked17]

I have a form that I need to allow a user to select/input both a date and time that will be applied to a single field in a table. The field for the table is formatted as "mm/dd/yy hh:nn".

Ideally I'd like to be able to allow the user to select the date using the pop-up date picker in Access 2007 and then enter the time in a masked text box and then have the two combined when saved to the table and naturally split when viewing in the form.

Can someone point me in the right direction?

Thanks!

 

[pl456]

Something like this as code on the click event of a command button. In this example I have used two fields in the table, a date field and a time field. Don’t think its worth combining them if you want to separate them again when people view it. This will pick up the value of control txt1 and put that into the date field of the table and txt2 into the time field of a table called tbl_dates. Rename everything accordingly.


Private Sub Command4_Click()

Dim sql1 As String
Dim tt1, tt2 As Variant

tt1 = Me.txt1.Value
tt2 = Me.txt2.Value

DoCmd.SetWarnings False ‘turns off warning message about insert query

sql1 = "Insert INTO tbl_dates (date, time)Values ('" & tt1 & "' , '" & tt3 & "' );"

DoCmd.RunSQL sql1

End Sub

 

[padlocked17]

Ok, I completely follow your line of thought.

What I think I'm trying to accomplish however is to keep the date and time in the same field to simplify date/time calculations in queries. I originally had the two fields separated one for time and one for the date, but then I figured it seemed liked it would be more difficult to take Date/Time 1 and subtract 14 hours from it to yield Date/Time2.

If I'm incorrect in this assumption please let me know. As it stands right now I've got several queries that use the DateAdd function such as in the SQL statement below. How would that work if I had two separate fields? I don't think simply concantating them would do the trick for the calculations would it?

UPDATE tblMissions SET tblMissions.SetByTime = DateAdd("h",-14,[LFATime]);

 

[vbaInet]

Just fyi, it is more advicable to use CurrentDb.Execute in place of Docmd.Runsql for action queries (i.e. Insert, Update and Delete). As I learnt it's faster and you don't need to turn on and off the Warnings (because it shows no warnings). You could try it on your code and check help for more options available.

 

[pl456]

Noticed I put tt3 in the sql string, sorry should have been tt2.

Anyway, You could just alter the part that reads

tt1 = Me.txt1.Value
tt2 = Me.txt2.Value

so it reads

tt1 = Me.txt1.Value & " " & Me.txt2.Value

which would combine the date & time fields

Alter the sql string to remove the redundant variable
sql1 = "Insert INTO tbl_dates (date)Values ('" & tt1 & "');"

 

[padlocked17]

Ok, that solves the issue of input, but then when it comes time to display the content again and possibly have it updated, would I simply use a Left() and Right() functions to break down the data to be displayed how I want or can you think of a better solution?

 

[padlocked17]

Alright I now have 3 tables (tblMembers, tblCrews, tblCrewMembers)

tblMembers
----------
MemberID (PK)
FirstName
LastName
PositionID

tblCrews
--------
CrewID (PK)

tblCrewMembers
--------------
CrewMembersID (PK)
CrewID (FK)
MemberID (FK)

As you can see there is only one field in the tblCrews table and acts as just a joining table.

What I'm trying to figure out now is how to display the names of the crew in a single row on a combobox identifying that crew with the last name of all of the members via an expression

 

[vbaInet]

Alright I now have 3 tables (tblMembers, tblCrews, tblCrewMembers)

tblMembers
----------
MemberID (PK)
FirstName
LastName
PositionID

tblCrews
--------
CrewID (PK)

tblCrewMembers
--------------
CrewMembersID (PK)
CrewID (FK)
MemberID (FK)

As you can see there is only one field in the tblCrews table and acts as just a joining table.

What I'm trying to figure out now is how to display the names of the crew in a single row on a combobox identifying that crew with the last name of all of the members via an expression

I think you should get rid of the CrewID (FK) field in the tblCrewMembers table. That table could look like this:

tblCrewMembers
--------------
CrewMembersID (PK)
MemberID_FK (FK)

I've postfixed MemberID with "_FK" so that you recognise it as a foreign key for when you want to do your queries and joins. It's a nice naming convention. I'm guessing that the personal details of the crews are in your tblCrews table?

 

[padlocked17]

I don't follow. If I take away the CrewID form the tblCrewMembers how am I associating members with each crew?

I really have nothing in the tblCrews table except the CrewID (PK) which is also acting as ID number identifing unqiue crews.

Personal details for members are in the tblMembers table and the only purpose of the tblCrewMembers are to tie members to a particular crew #.

 

[padlocked17]

Dang it, looks like I mixed responses to different threads ... my real question along this thread is :

When it comes time to display the content again and possibly have it updated, would I simply use a Left() and Right() functions to break down the data to be displayed how I want or can you think of a better solution?

 

[gemma-the-husky]

if you have a date and a time, you can just add them

ie fullvalue = dateonly plus timeonly

however, you need to understand how the date datatype works

data is an integer number
time is a fractional part of a day.
the whole is a real (double) number


note that a time value of more than 24hrs will display as the time element only (ie modulo 24hrs) - but may ACTUALLY store a number of days - so adding the date and time may give a strange answer.

a date is actually stored as say 38061.125

38061 days since 30th December 1899
.125 is an eighth of a day - ie 3hrs

 

[padlocked17]

Ok, trying my hardest to wrap my head around that.

I think ideally I'd like to keep the date and time stored in one field and then break it down for viewing and data entry and that I suppose is where my problem is lying.

I've figured out that taking two separate fields and concantating them can allow me to insert the date/time into a single field, but I'm a little unsure still of how to break it down for viewing/editing at a later time in the form.