Can a query convert a field value into number of records?

[ifjzettner]

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]

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".

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

 

[ifjzettner]

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.

 

[JHB]

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]

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]

Try to send your new database, (zip it because you haven't send 10 post).

 

[ifjzettner]

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).

 

[JHB]

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]

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]

You're welcome, good you got it to work.