Can a query convert a field value into number of records? (1 Viewer)

ifjzettner

New member
Local time
Yesterday, 18:04
Joined
May 11, 2013
Messages
8
Hello all,

Thanks for opening my thread.

I build a database to manage some events.
To simplify things, let's assume an event takes place on 3 days, from 01-05-2013 to 03-05-2013.

I create a table named EVENTS.

Among other stuff, I create the following fields:
EVENT_first_date
EVENT_last_date

Then I create a query which returns the number of days the event will take place on.

EVENT_total_days: [EVENT_last_date]-[EVENT_first_date]

I use an append query to insert this new record into the table EVENTS_MAIN.

My problem starts here.

I wish to set the start time and end time for all event days in hours and minutes. I will use these entries for displaying start time and end time for each event days, to calculate daily total hours and event total hours later on.

Therefore, I wish to create a query that will display event date 1, event date 2 and event date 3, based on the number of the EVENT_total_days field, so I can set the start time and end time for each day separately.

Is it possible to "convert" or "switch" the number of the days into number of records? Can a query convert a field value into number of records?

Thank you for your suggestions.

Best wishes,
Tamas
 

JHB

Have been here a while
Local time
Today, 02:04
Joined
Jun 17, 2012
Messages
7,732
Hi Tamas, you can use a UDF (User Defined Function), in the query to add the records. I've attached a small database, run "Query2".
Code:
Function CreateTimeTable(StartDate As Date, EndDate As Date, EventId As Long)
  Dim dbs As Database, x As Long, Adate As Date
  
  Set dbs = CurrentDb
  
  'Delete
  dbs.Execute ("Delete EventId FROM EventDayAndTime " _
  & "WHERE EventId=" & EventId)
  For x = 0 To DateDiff("d", StartDate, EndDate)
    Adate = DateAdd("d", x, StartDate)
    dbs.Execute ("INSERT INTO EventDayAndTime (EventID, TheDate) " _
    & "VALUES (" & EventId & ", #" & Format(Adate, "mm/dd/yy") & "#)")
  Next x
  CreateTimeTable = DateDiff("d", [StartDate], [EndDate]) + 1
  dbs.Close
  Set dbs = Nothing
End Function
 

Attachments

  • CreateRecords.accdb
    488 KB · Views: 90
  • CreateRecords.jpg
    CreateRecords.jpg
    84.6 KB · Views: 546

ifjzettner

New member
Local time
Yesterday, 18:04
Joined
May 11, 2013
Messages
8
Hi Tamas, you can use a UDF (User Defined Function), in the query to add the records. I've attached a small database, run "Query2".

Hello JHB,

Thank you very much for the detailed explanation.
Could you please help me further?
Without modifying anything, EventDayAndTime returned strange results for me.

Without running the Query, in the original file I read normal short dates in the EventDateAndTime table. But when I run Query2 for testing, the dates become 5:01:13, 5:13:13 and so on.
Is it a compatibility issue? The IDs are fine.

And, if it helps, I'm using the Hungarian date format, which is yy-mm-dd. I tried modifying the code, but it just only switched the format to 13:05:01 and so on, as expected.
 
Last edited:

JHB

Have been here a while
Local time
Today, 02:04
Joined
Jun 17, 2012
Messages
7,732
Hmm - separators like ":" are normally used in time format.
Try empty my tables and put in you own dates, what happens then, (dates can be difficult to deal with for date format which is different from US-format)?
Else, try build the 3 tables from scratch in a new database.
Only for info, the Danish date format is dd-mm-yyyy.
 

ifjzettner

New member
Local time
Yesterday, 18:04
Joined
May 11, 2013
Messages
8
Hmm - separators like ":" are normally used in time format.
Try empty my tables and put in you own dates, what happens then, (dates can be difficult to deal with for date format which is different from US-format)?
Else, try build the 3 tables from scratch in a new database.
Only for info, the Danish date format is dd-mm-yyyy.

Thanks for the tips.

Tables emptied, own dates put in, no change.
New database built for scratch, same procedure, no change.
Computer internal clock set to dd.mm.yyyy, no change.

In the table EventDayAndTime, the TheDate field is not recognized as a date. If I change the date into long date, it returns 1899. If I change it to long time, it returns the same results: 13:05:01 and so on. So this is definitely a time format, not a date.

I tried to check the code but I'm no expert here. What I try to understand is, why the date is ignored and time is put in, converted from the normal date format.

Any suggestions?
 

JHB

Have been here a while
Local time
Today, 02:04
Joined
Jun 17, 2012
Messages
7,732
Try to send your new database, (zip it because you haven't send 10 post).
 

ifjzettner

New member
Local time
Yesterday, 18:04
Joined
May 11, 2013
Messages
8
Try to send your new database, (zip it because you haven't send 10 post).

Here it is.
I changed date format to yy.mm.dd in the code, and set short date in the EVENTS table, others are unchanged.

I searched Google and it returned an article "Date comes out as Time after using DATE in VBA". Maybe there is useful stuff (can't post links yet).
 

Attachments

  • CreateRecords_v2.zip
    28.4 KB · Views: 89
Last edited:

JHB

Have been here a while
Local time
Today, 02:04
Joined
Jun 17, 2012
Messages
7,732
By me your database run ok, result come out as expected, but try, (maybe play a little around with the "mm/dd/yy"):
..#" & CDate(Format(Adate, "mm/dd/yy")) & "# ..
 

ifjzettner

New member
Local time
Yesterday, 18:04
Joined
May 11, 2013
Messages
8
By me your database run ok, result come out as expected, but try, (maybe play a little around with the "mm/dd/yy"):

SOLVED.
Solution is, must put the "-" separator between the year, month and day values. Thank you very much!
 

JHB

Have been here a while
Local time
Today, 02:04
Joined
Jun 17, 2012
Messages
7,732
You're welcome, good you got it to work. :)
 

Users who are viewing this thread

Top Bottom