Thank you so much for the detailed explanation and finding time to correct the code.
I have tested the new code and got some errors then did some finetuning as the data has column headings. The actual records start from Row2.
changed cell reference to A2 wherever A1 for sheet("test") mentioned. Also 'For lngRow = 1 To....' changed as 'For lngRow = 2 To...'
Now the records are duplicated with 5 minutes interval, but...
jackjee
Last Activity: Jun 29 '22, 07:32 AM
Joined: Dec 23 '20
Location:
-
Dear NeoPa
Thank you for the guidance and time you are giving.
My apologies about the time interval interpretation and happy to take your advise to keep the counts for 0600-0605, 0605-0610 etc.
To get the counts in that way, I dont know how to deal with my data which has 2 time fields and as I was not sure how to deal with this from my Access Table, I thought I will do the record split from Excel and then I can import the data...Leave a comment:
-
Thank you for the quick reply.
the time intervals I am looking is basically minutes rounded, actual data will have time with seconds as hh:mm:ss.
The column headings are basically count from eg. 06:00:00 to 06:04:59 and next column start as 06:05:00 to 06:09:59.
So the count will be anything between this buckets, That is the reason I referred as 06:00-06:05 then 06:06-06:10
As you mentioned this will be complicated, is...Leave a comment:
-
Crosstab query for time duration from 2 fields
Hi All
I have a table with records having timein and timeout from terminal. I am trying to find count of the buses in 5 minutes buckets of the hour for each terminal.
data sample:
Bus terminal Timein Timeout
GGT rq1 20-01-22 05:56 20-01-22 06:11
CDC rq1 20-01-22 05:58 20-01-22 06:16
HFT rq2 20-01-22 06:18 20-01-22 06:39
ABC rq3 20-01-22 06:42... -
Crosstab query based form data update
Hi
I have a continuous form based on crosstab query results. The fields in the form is from the crosstab column headings.
I am looking for updating the records directly from the form such as on click of that item in the record need to be updated from a pop up/or values placed on the form header (as textbox or command buttons)
The structure of the record is like below
The column headings are such as Date, Apple,... -
Hi NeoPa
Thank you for the quick reply and apologies for creating a confusion and unclarity with my request.
The main table has fields such as Staff, Taskdate (as "dd-mmm-yy"'), Duration ("hh:nn"), Tasks etc.
A select query is used to select all the fields and an additional custom field for converting the duration to seconds with the use of a function I got from google search.
Based on this select...Leave a comment:
-
Sum of short time values in report footer
Hi
I am trying to sum the field values in an access report footer. The source is from a cross tab query and the values are in the format of hh:nn, but some of the values are greater than 24 hour such as 77:50, 24:05 etc.
I have unbound textboxes placed in the access report footer and expected this text boxes need to give me the sum of each staff. I tried to set the control source as =sum([staff1]) etc. but no luck
Hope someone... -
Hi NeoPA & experts
Ref. my post, I found a solution for my post, which is looking one specific path and merge all files and provide an output with all files merged. This is ok, but I dont know how to read multiple paths with below code at the same time and merge all files and provide single output.
Also, another question related to this, after importing this merged data to an access table I want to delete the duplicate records based...Leave a comment:
-
Import Text files from multiple folders
Hi
I am looking for a vba solution to import multiple txt files at the same time to an Access DB.
While importing the filenames need to be stored in another table so next time these files should not be imported.
As an example,
i have multiple folders (can specify the paths in the module) where these text files are available (new files are copied by someone and store to these paths in a daily basis).
When I execute... -
Hi ADezii
Thank you for the support even though in your busy schedules. I had a quick look and it looks fine. Hope this will work for me. I will try with actual data and confirm soonLeave a comment:
-
Hi cactusdata
Thank you for the provided function. I used it in a query and it looks like it is giving expected results
The syntax of query as "SELECT data.alphabets, ExtractThreeOrM ore([alphabets]) AS Result
FROM data;"
The sample records in the table as below
Field name: alphabets
1NTRWE
NNB3456
7DCXD
NNB67FX
DUC6
LIY683
LIY9RT
MM2345...Leave a comment:
-
Extract from string
Hi All
I have a VBA code in access which I am using to split a string and inserting the results to a table.
the string will have alphabets and numbers. If the first 3 characters are alphabets and the 4th character is a number I need to extract the first 3 characters. And if the string has all alphabets or numbers or numbers before 3rd characters, insert the same string to the field. But if the string has 3 alphabets, then numbers and... -
Hi ADezii
Apologies for delayed reply. Happy New year.
I was on off days and back to office. I am trying with the 'Rolling Hours_4' DB and now the challenge is I have multiple days data so I need to add the date in one field for the corresponding rolling hours (288 records) in the query 'qryGetTotals' which gives the final result.
The date will be available in the 'timeat' field of the main table. Hope my explanation is clear...Leave a comment:
-
Hi Rabbit
Apologies if I am not getting it clear.
Do you mean to say that run an update query to update the 'TimeStart' and 'TimeEnd' field of 'TimeSlots' table as below syntax "UPDATE TimeSlots SET TimeSlots.TimeS tart = TimeSerial(Hour ([timestart]),Minute([timestart]),Second([timestart])), TimeSlots.TimeE nd = TimeSerial(Hour ([TimeEnd]),Minute([TimeEnd]),Second([TimeEnd]));"Leave a comment:
-
Hi Rabiit
Thank you for the guidance to 'run a one time update on the TimeStart and TimeEnd fields in the time slots table', but I am not sure how to do that. And on the reply you have mentioned that 'count arrivals that come at the start of the day to also count at the end of the day and vice versa' which is not clear to me. I want the counting from 0000 until 2359 of the same date. So when the rolling reaches 2300-2359 midnight, it can stop....Leave a comment:
-
Hi ADezii
It looks like the latest DB code is working fine. I changed couple of entries time and executed the code and the rsults are getting as expected, but need to try with more data which I will do tonight and let you know how it isLeave a comment:
-
Hi ADezii
I tried with the sample file I posted above for Rabbit, but the results are not populating after 2300 hr slots. Then I tried Rabbit solution and it has some small issues which I already mentioned.
The purpose of this DB is to store the date and times of buses which are arriving at a specific bus station. The query I am looking is to find the hours (not static hours such as 0000-0059, 0100-0159 etc but as I mentioned take 5...Leave a comment:
-
Hi Rabbit
Sorry for delayed reply. I tried the provided DB with some sample records. only issue I noticed that when the time is 5,10 15 etc. then that record is not counted. I am attaching the DB with my sample data for your kind check. Please look at the timeslots 0105-0205 where the query results 6, but actual record count should be 7.
Also at 0155-0255 record count shows as 0 but it should be 1 and in 2145-2245 it shows 1 but actual...Leave a comment:
-
Hi ADezii
Thanks for the quick reply. I am not at all in hurry. I am so happy that there is a solution for my request. I will wait for your replyLeave a comment:
-
Hi ADezii
Thank you for taking your valuable time on this.
Post#9, yes I was looking for that option with 288 columns, but if there is a limitation, I can go with the latest example you have provided in last reply which is populating in the 'Rolling Hours_2' DB.
My final goal is to get the timeslots populated dynamically based on the data. As per your last reply, hope it will be doable. Thank you for the kind support and will...Leave a comment:
No activity results to display
Leave a comment: