DMax + 1 Hour

Jul 1, 2006

I've searched the forum and can't quite find what I need so I'm asking for further guidance form all you Access masters out there!

I have a field 'Time' in the format of 'dd/mm/yyyy hh:mm' which has a default value of =DMax("Time ","[Monitoring]")+1.

This increments 1 on the 'dd' bit when each new record is created, however what I want is for the hour (hh) bit to increment 1 on each new record.

Not sure if I'm being niave but I thought it was some sort of formatting issue, so I've dabbled with a few but to little effect unfortunately.

View Replies


ADVERTISEMENT

Hi, Need Help With Subforms Within The Hour

Feb 11, 2008

Hi, I have a main form with a subform inside it. The subform contains two records but only one is shown during form view, is there any way to show both records at the same time?

I am using continuous forms view for my subforms as i have to.

View 3 Replies View Related

Group By More Than 1 Hour

Jul 25, 2005

i have a table of time and dates and a value after it inn which i want to do a query which groups these values by every 3 hours e.g

my table
12/02/2001 00:00 6
12/02/2001 01:00 4
12/02/2001 02:00 8
12/02/2001 03:00 12
12/02/2001 04:00 7
12/02/2001 05:00 6


i want the query to do this
12/02/2001 02:00 18
12/02/2001 05:00 25

i cant find anything anywahere and its use ids for a chart in which the chart wizard allows grouping by 1 hour and not by more than 1 hour any help will be much obliged

cheers

View 1 Replies View Related

Convert Number To Hour

Jan 2, 2006

Hello Everybody...

I have a Question.
How do I convert a number (26) to 26:00 Hours.

The thing is.
I have a database that calculates the hours of overtime.

for example.

I work from 7:00 to 19:00.
Then I get 12:00 hours of total time.

The above is no problem.

But no I want to deduct the number 2 from 12:00 hours.

I hope to hear from u guys soon

View 2 Replies View Related

Adding One Hour Onto Time

Jun 14, 2007

Hi,
ive searched the forums for this but cant find anywhere.

how can i add one hour onto a time stamp please.

many thanks:confused:

View 4 Replies View Related

Day/Hour/Minutes Query

Nov 16, 2005

Can anyone tell me if there is a way of displaying a number, which represents the number of minutes of elapsed time, into the format days:hours:minutes elsapsed?

For instance the number (in minutes) 1530 would display as = 01:01:30. 1 day, 1 hour & 30 minutes of elapsed time.

Any help would be appreciated.

Paul.

View 2 Replies View Related

Number Of Hour Worked

Jun 3, 2006

Hello!!!
I've a trouble:
I have an Access DataBase with two tables:
1--Workers with fields (ID, Name and $/hour)
2--Marks with fields(ID, WorkerID, Datetime of mark)

Well, I need to generate a report with the following structure:

Name Worker / Number of days worked / Total Cost in $
---------------------------------------------------------------------------
Worker 1 / 19 / 13000$
Worker 2 / 20 / 21354$
Worker 3 / 12 / 12000$
...

Example of Workers Table Data:
ID / Name / $/hour
-------------------------------------------------------------------------
21367 / Worker 1 / 21,34$
54883 / Worker 2 / 54,87$
87364 / Worker 3 / 5,38$

Example of Marks Table Data:
ID / IDWorker / DateTime-Mark
-------------------------------------------------------------------------
6754-54 / 21267 / 19/05/2006 8:05:32
4327-12 / 54883 / 19/05/2006 8:07:12
7645-98 / 87364 / 19/05/2006 8:13:54
5432-99 / 21267 / 19/05/2006 14:05:32
7584-23 / 54883 / 19/05/2006 14:07:12
9485-23 / 87364 / 19/05/2006 14:13:54
... / 21267 / 19/05/2006 16:05:32
... / 54883 / 19/05/2006 16:07:12
... / 87364 / 19/05/2006 16:13:54
... / 21267 / 19/05/2006 20:05:32
... / 54883 / 19/05/2006 20:07:12
... / 87364 / 19/05/2006 20:13:54
... / 21267 / 20/05/2006 8:05:32
... / 54883 / 20/05/2006 8:07:12
... / 87364 / 20/05/2006 8:13:54
... / 21267 / 20/05/2006 14:05:32
... / 54883 / 20/05/2006 14:07:12
... / 87364 / 20/05/2006 14:13:54
... / 21267 / 20/05/2006 16:05:32
... / 54883 / 20/05/2006 16:07:12
... / 87364 / 20/05/2006 16:13:54
... / 21267 / 20/05/2006 20:05:32
... / 54883 / 20/05/2006 20:07:12
... / 87364 / 20/05/2006 20:13:54

The condition to increment number of days is:
YEAR(date-mark) != YEAR(date-mark) OR
MONTH(date-mark) != MONTH(date-mark) OR
DAY(date-mark) != DAY(date-mark)

for example:
1)
19/05/1982...time-mark // Worker 1
19/05/1982...time-mark // Worker 1
19/05/1982...time-mark // Worker 1
19/05/1982...time-mark // Worker 1
-----------------------------------> 1 days worked
2)
20/05/1982...time-mark // Worker 1
20/05/1982...time-mark // Worker 1
21/05/1982...time-mark // Worker 1
21/05/1982...time-mark // Worker 1
-----------------------------------> 2 days worked

Do you understand it?

Well, I've tried several SQL sentences, but I can't make the definitive sentence. Can you help me, please?

Note --> each Mark-Datetime represents an instant of time!!!

View 1 Replies View Related

Format For 24-hour Time

Mar 31, 2008

Okay,

Missing something simple here and can't figure it out. I want to display a time in military 24-hour format but can't get it to show the first "0" between the times 0000 - 0959.

Current statement is:

datTripTime = Format(datArr, "HH:mm")

What am I missing please???

Thanks

View 3 Replies View Related

Query Data By Hour

Jul 28, 2006

I have a very large dataset with over 11 million data records in three different databases! Currently the data is collected on average every minute, which is much too detailed! I would like to pull data for every site on the hour, or somewhere near the hour as some data was collected at different time intervals. (this latter part is a completely different issue, I would be happy just to get my first query working, then deal with data not collected exactly on the hour)

I wrote a query and tested it out on a very small portion of the data. It seems to be working, but for some reason won't pull data for the 2 pm, 5 pm, or 8 pm hours...even though the data is there in the table. Any reason why this may be? Even if I simplify the query to only pull out those hours, and not all 24 at once, the data still doesn't get selected. I really can't think of why it's not working. Any suggestions?

I've currently set up the Criteria in the time column of the query to look like this:
#1:00:00 AM# Or #2:00:00 AM# Or #3:00:00 AM# Or #4:00:00 AM# Or #5:00:00 AM# Or #6:00:00 AM# Or #7:00:00 AM# Or #8:00:00 AM# Or #9:00:00 AM# Or #10:00:00 AM# Or #11:00:00 AM# Or #12:00:00 PM# Or #1:00:00 PM# Or #2:00:00 PM# Or #3:00:00 PM# Or #4:00:00 PM# Or #5:00:00 PM# Or #6:00:00 PM# Or #7:00:00 PM# Or #8:00:00 PM# Or #9:00:00 PM# Or #10:00:00 PM# Or #11:00:00 PM# Or #12:00:00 AM#

Is there a better way to go about this?
Thanks!

Ok, I am still interested in knowing why the above query wasn't working should anyone know. But while thinking of how to deal with my other data not on the hour...seemed to have made a few queries that do the trick! Access is great...but sometimes very slow to get what you want done when you're still learning!

View 1 Replies View Related

Counting Values Into 1 Hour Bins

Jul 16, 2005

MY QUESTION:

I’m using Access 2002.

I’m trying to create a query that will COUNT the number of values within 1 hour time bins (e.g. 00:00 to 00:59, 01:00 to 01:59 etc). The catch is that I need to have every hour bin represented whether there is any value in it or not. I believe that I need to create a separate table of the bins that I want (include bin start-value & stop-values) and then bring this table into my query and join it to the original table.

I was able to construct the make table query below to COUNT the values in hour bins for which there were records, but I also need rows for all of the zero values;

SELECT [All NWHI Detections].Species, [All NWHI Detections].Transmitter, [All NWHI Detections].Island, [All NWHI Detections].Location, DatePart("yyyy",[All NWHI Detections.Date]) AS [Year], DatePart("m",[All NWHI Detections.Date]) AS [Month], [All NWHI Detections].Date, [All NWHI Detections.Date]-[SharkTaggingSummaryTable.DateDeployed] AS [TimeSinceTagged(days)], CDate2Julian([All NWHI Detections.Date]) AS [Julian Date], DatePart("h",[All NWHI Detections.Time]) AS HourBin, Count([All NWHI Detections].Time) AS TotalNumberofDetections INTO [GLM HourlyBins]
FROM SharkTaggingSummaryTable INNER JOIN [All NWHI Detections] ON SharkTaggingSummaryTable.Transmitter = [All NWHI Detections].Transmitter
GROUP BY [All NWHI Detections].Species, [All NWHI Detections].Transmitter, [All NWHI Detections].Island, [All NWHI Detections].Location, DatePart("yyyy",[All NWHI Detections.Date]), DatePart("m",[All NWHI Detections.Date]), [All NWHI Detections].Date, [All NWHI Detections.Date]-[SharkTaggingSummaryTable.DateDeployed], CDate2Julian([All NWHI Detections.Date]), DatePart("h",[All NWHI Detections.Time])
HAVING ((([All NWHI Detections].Species)="tiger"))
ORDER BY [All NWHI Detections].Transmitter;


Q1? Creating the hour bins table

This table will be very large because it will need to have 4 years worth of hour bins for each transmitter (N=15) and location (N=12). This results in 6,307,200 hour bins! How can I write a query to create this table, or is there a better way of doing this?

Q2? Bringing the hour bins table into the main query.

How do I include the hour bins table in the main query to get my final result.


Any help would be greatly appreciated! Thank you.

View 1 Replies View Related

Group By Hour Of Day And Count Records

Apr 24, 2006

is there a way to count - in one query - the number of records that fall between specific times:

Time CountOfRecords
0000-0100 7
0100-0200 2
etc
2300-2400 4or do i have to do separate grouping queries then separate counting queries for every hour of the day? a crosstab? something else perhaps? feels like i'm missing something obvious (i hope...).

View 2 Replies View Related

Run A Create-table Query Every 2nd Hour

Sep 18, 2006

Hi
I have made a create-tabel query that I want to run every 2. hour. How can I set Access up to do this automatically?

eroness

View 1 Replies View Related

Query Maximum Number In Hour Per Day Every Cell ID

Jul 13, 2015

I need to know about "Need to query result from access2007 find only "one maximum number" in hour per day every cell ID"

How I use sql command?

See my file at ...

View 5 Replies View Related

Queries :: How To Round Off Times To Nearest Half Hour

May 23, 2014

I am trying to round off times to the nearest half hour. To be clear, I don't want to only round down or only round up. I need the rounding to be to the nearest half hour. I want to do this in the query, not vba. I've attached a picture of the query.

View 3 Replies View Related

Need Ideas: Alert User When Record Hasnt Been Updated In Over An Hour

Jul 22, 2007

I have a form in continuous forms mode...
Each entry is something the user is supposed to check on once and hour and update.
I have a "Last updated" field.
Now() goes into that field every time the record is updated.
I have a querry that will show the users records that are over an hour old.

How can I get Access to pop-up a window or something to alert the user they need to look at something? It would be really nice if Aspect would start blinking in the task bar or something.

Thanks!

View 5 Replies View Related

Forms :: Sum Numeric Field On Form If Day And Hour Fields Are Null

Jan 8, 2014

What is wrong with this query?

=IIf(IsNull([OffPeakDay],IIf(IsNull([OffPeakHour],Sum([Import]-[Export]),0)))

What I want is to sum the field (Import minus Export which are numeric) on the form if the OffPeakDay AND OffPeakHour fields are null.

View 11 Replies View Related

Modules & VBA :: Display On Form Average Records Per Hour Added To Database

Sep 29, 2014

I am trying to display on a form the average number of lines/records per hour that a person is adding to the database.

Trying to work with Dfirst and DLast I'd subtract the time of the most recent record from the time of the first record entered and then divide the number of records to display the average per hour in a text box.

View 14 Replies View Related

DMAx Help

Sep 15, 2006

Thanks for all the help to date.

Problem trying to get a dmax field for an invoice no in a query - am doing something wrong current attempt below won't work

Expr1 :dmax([Invoice no],[Table name]![Invoice no])

what am I missing - do I need something on the end when I get this to work I will use it to add 1 for the final query prior to print

View 4 Replies View Related

Help With DMax!!!!

Dec 21, 2006

Okay...I know the basic syntax for Dmax (or at least I thought I did) but I cannot get this statement to do what I want!

In the <before update> event I tyoed this:

Dim RNum As Long
Me.txtRNumber = DMax("[Request_Num]", "test_request") + 1
Me.txtRnumber = RNum

txtRNumber is the field on the form
Request_Num is the field on the table
test_request is the table

yes...I know the name syntax is bad (it's an older db I'm using as a test for a newer one).

Obviously, I want to automatically increment the field value by 1 on every new record (an if newrecord clause is included). But I just can't get the stinkin thing to work!!!

View 12 Replies View Related

Max, DMax Etc

Oct 3, 2006

I am having difficulty understanding under which circumstances I should be using Max() as opposed to DMax() functions. Both seem to do the same thing (find maximum value in a field), but DMax has additional arguments.

What I currently want sounds like neither of these, as I want to query the maximum of several values in various fields for a specific record, rather than the maximum of several records in a specific field. Is there a way of doing this otherwise than by nesting IIF functions several deep?

I keep running into "Microsoft Office Access has encountered an error and needs to close" error messages, and am coming around to the view that I may need a UDF. I have never done one in Access before (but did some time ago in Excel).

Currently I have fields in various tables:
T_Clients.Commencement
T_Clients.Cessation
T_Tasks.StartDate
T_Tasks.EndDate

There is a one (T_Clients.ID_Clients) to many (T_Tasks.ID_Clients) relationship
T_Clients.ID_Clients and T_Tasks_ID_Tasks are primay keys.

In "pseudocode" my query, probably via a UDF, needs to return:

Min(Max(StartDate,Commencement)+12months,Min(EndDa te, Max(Cessation,StartDate))+9months

Commencement and Cessation may contain Null values, which are then to be treated as #1900-01-01# for the purposes of any comparisons.

My first attempt at this was to create several queries, each based on the previous query. Inefficient, perhaps, but I could trace the logic. I started by creating queries that substituted #1900-01-01# for null values in the Commencement and Cessation fields.

However when I got about 3 queries deep I ran into the "Access has encountered an error and needs to close" error, and no amount of playing around with it stopped that.

I don't know if relevant but I don't *really* have a T_Tasks.StartDate field. I have a query that calculates the start date from the previous EndDate, but I did not want to complicate the problem specs. That query seems to work ok. If I get a solution to the above I hope to jigger it to fit.

View 3 Replies View Related

Dmax And Its Format

Oct 13, 2004

I want to create fields that are in the form IC0000001, IC0000002, etc. I can do the increment in the numbers by using the dmax function but how do I add the alphabets before it? How can I start with 0000001 instead of 1? Any help would be greatly appreciated, thanks so much!

Swee

View 3 Replies View Related

Dmax - To Return Not Quite The Highest

Jan 18, 2006

Hello,

I am using DMax() to return the highest date in a table, but really need it to return the second-highest...so, if I had:

14th Feb
10th Dec
1st Jan

I don't want it to return 10th Dec, I want it to return 14th Feb

Any help much appreciated. I am sure there was something like dmax()-1, but that just returns (in my example above) 9th Dec!

Thanks!

View 4 Replies View Related

New Entry Button DMax

Dec 4, 2006

Hello peeps!

Just someone advice if I can!

I have a table that contains student details:

StudentNo
Surname
Forename

I have created a form to add students to this table but I need a button that will read the highest StudentNo and then add one, putting the new stundent number in the form in a new record?

How can I go about doing this? Can it be done using a macro or do I need to use VBA??? I have been told that I would need to use the DMax function but I dont know VBA.

Thanks in advance for all your help

View 4 Replies View Related

DMAX In A Text Field

Mar 24, 2008

Hi

I have a table with three key fields

table name is tblSampleDetails

Field names are

ClientSampleRef
BatchNumber
Visual

A Batch may have many samples of which some may be visuals

Currently I store a variety of values (some number, some text and some a combination of both) in ClientSampleRef but I would like to start on some samples creating the value for the ClientSampleRef
If the sample is a visual (this is indicated by a check box on the form giving a value of -1) I would like to make the ClientSampleRef = Visual 1 this would then increment within the same batch by one each time.
I have taken some code from Dev Ashish and Arvin Meyer and changed the field and table names but I am getting an error saying there is a missing operator

If Check42 = -1 Then
Me.ClientsSampleRef = "REC-" & Right(DMax("ClientSampleRef", "tblSampleDetails"), Len(DMax("ClientSampleRef", "tblSampleDetails")) - InStr(1, DMax("ClientSampleRef", "tblSampleDetails"), "-")) + 1
End If

I am just testing this code so I have created a record with a value of REC-1 in the field ClientSampleref
I have also created a test database and the code works within that upto a value of REC-10 then I get problems

any help or pointers greatly appreciated

Richard

View 1 Replies View Related

DMax And DateDiff Func

Jul 13, 2005

HI All,

I'm using
Between DMax("DateFieldName","TableName")-10 And DMax("DateFieldName","TableName") expression

in query to find time interval 10 days prior the last record puting this into Date field in my query Criteria. It's working fine.
But I want to find time interval a Month and an Year prior the last record.
I'm thinking to use DateDiff func to calculate properly intervals.

Between DMax("DateFieladName","TableName")-DateDiff ("m",1-2-100,1-1-100) And DMax("DateFieldName","TableName") --> for a month

That function also work but I'm not sure if it calculate correct result

Any better ideas? Thanks!

:cool:

View 3 Replies View Related

DMax() For Sequential Autonumber

Jul 23, 2005

Hello Access friends,
Trying to have a sequential autonumber for the ScreenID with the DMax () function.
Please advise on what is wrong with the following :
=Nz(DMax("[ScreenID]","[Screenprep]","[ScreenID] = '" & [CarModel] & "-" & Left$([Category],1) & "'")+1,0)
Neither putting this code in control source or beforeupdate event of the form is not working.

I have looked around and from previous posts in the forum come up with this module. But it is not working either:
Public Function NewScreenID() As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim ScreenID As String
Dim CarModel As String
Dim Category As String

On Error GoTo Err_Execute

Set db = CurrentDb()


Set rst = CurrentDb.OpenRecordset("SELECT Max([Screenprep].[ScreenID]) AS MaxScreenID from [Screenprep];", dbOpenSnapshot)

If IsNull(rst!MaxScreenID) Then
NewScreenID = [CarModel] & "-" & Left$([Category], 1) & Format(1, "0000")
Else
NewScreenID = rst!MaxScreenID + 1
End If


rst.Close
Set rst = Nothing
Set db = Nothing

NewScreenID = ScreenID

Exit Function

Err_Execute:
'An error occurred, return blank string
NewScreenID = ""
MsgBox "An error occurred while trying to determine the next sequential number to assign."


End Function

In advance thank you for your time.Can someone please guide me on how to sort this out?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved