Hi All,
I am on a mission to get this out but having muchos problemos! Hope someone can hand me a little tip or pointer.
My table has two coloms:
Date_stamp(datetime), product_code(text(25))
I need to show this data in a report with a third colum that shows the difference in seconds between each record. IE how long it took for the next date-stamp to occur.
Can anyone help.
Thanks in advace
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
I have a database with date and time each stored in a separate field. Now I want to query the database based on a start date/time and an end date/time. I started with the code below but it only returns events within the same time range on each day when what I really need is every event from a specified date and time through a specified date and time.
SELECT myTable.ID AS myTable_ID, myDate, myTime, FirstName, LastName
FROM Staff INNER JOIN myTable ON
Staff.ID = myTable.StaffID
WHERE myTable.myDate >= #3/2/2014#
AND myTable.myDate <= #3/3/2014#
AND myTable.myTime >= #8:00PM#
AND myTable.myTime <= #11:00PM#
ORDER BY myDate desc
In the above example what I want is every event from 3/2/2014 8:00PM until 3/3/2014 11:00PM. But what I get instead is every event between 8:00PM and 11:00PM on 3/2/2014 and every event between 8:00PM and 11:00PM on 3/3/2014.
Hello - I have a database that is almost finished but unsure on the final calculations and how to get the correct data to flow to the next payperiod.
Each payperiod someone enters all staff members hourly time, vacation time used, sick time used, etc... Depending on the staff members status (fulltime or parttime), they accumulate a certain amount of sick and vacation hours but can never exceed the maximum in any given payperiod.
I have a query (which is dependent upon another query) that calculates the beginning sick time, sick time used, sick time earned, and gives me the remaining sick time and it works fine for the first payperiod.
However, I do not understand how I can get the remaining sick time to become the beginning sick time for the next payperiod. Is this done in the same query? A different query? A different method?
I have been beating my head against the wall for several days. Any guidance would be greatly appreciated!
I have attached a sample database. Basically I want to have some lines of code that generate the result table, which is tbl_readmit_result.
As you can see, the difference is the addition of a new column called re_admit_status.
Rule is:
Status = "y" when the admission date, compared to the previous discharge is less than 7 days, otherwise "n", for the same pt_id. You cannot compare the two dates on different pt_ids.
Is there a way that this can be done automatically without having to go through the record manually?
I have a report that looks up a previous date then finds the data that goes with that date. I went to run the report and the data now is blank. I am thinking it is because of the formula is looking for 1/28/2013 instead of 1/31/2013.
Code:
DLookUp("[Net Insured]","[CashFlow Link]","[RCNAME]=" & "'" & [RCNAME] & "'" & " And [Date1]= DateAdd('m',-1,DateSerial(Year(Date()),Month(Date())-0,0))")
I don't know why this error message appear.
this message appear when I a lookup function is run.
The code statement that genrate this error for me is:
CName = DLookup("Name", "VisCardHolders", "Indx=" & CIndx)
Dim CIndx as long
CIndx=50025
The VisCardholders is a recordset consists of two fields:
CIndx
Name
One of the exist records in the [VisCardholders] is
CIndx=50025
Name: James Southern
.I tried to find the reason of this error but unfortuantelly, I found nothing.
Please tell me.
Hi,
I have the following code:
Private Sub PLPREMCAL_Click()
Dim ColumnNumber As Integer
ColumnNumber = [NoWorkingDirectors] + [NoManualEmployees] + [NoPriciples]
If [LOI] = "2.6m" Then
PLPremium = DLookup("[" & ColumnNumber & "Emp]", "2_6m", "[Business]=Forms![Form1]![Business]")
ElseIf [LOI] = "3.9m" Then
PLPremium = DLookup("[" & ColumnNumber & "Emp]", "3_9m", "[Business]=Forms![Form1]![Business]")
ElseIf [LOI] = "6.5m" Then
PLPremium = DLookup("[" & ColumnNumber & "Emp]", "6_5m", "[Business]=Forms![Form1]![Business]")
ElseIf [LOI] = "10m" Then
PLPremium = DLookup("[" & ColumnNumber & "Emp]", "10m", "[Business]=Forms![Form1]![Business]")
End If
End Sub
LOI is coming from a Combobox on my form. When I change the LOI I want the PLPremium value on my form to up tho the value in that table.
It's so frustrating - I keep getting this run time error
Would REALLY appreciate any help.
I have a large table which is updated everyday. Each record will have a different date but there will be many with the same account number. I have a query which will pull one record from each account number according to a user specified start date, another query which will pull by end date, and a third query which uses both to do a calculation on another field. The problem is, if one of the first two queries does not find a record with that date then the calculation will not be done because there is no record. What I need is a way to go back one day if the specified date does not exist. I have tried using an iif statement in a report with the DateAdd function in the true part. Here is what I have
IIf([blinks_test_end]![Reading_Date]=Null,DateAdd("d",-1,[blinks_test_end]![Reading_Date]),[blinks_test_end]![Reading_Date])
[blinks_test_end]![Reading_Date] is the user specified date. When I run the report it asks for blinks_test_end, I think it doesn't recognize that this is the user specified date.
Hi all,
I was wondering if somebody could help me with a query I'm struggling to write. I have written a query which shows customers who have booked capacity of my company's product. I have written another query which shows customers who have bought volumes of the product. I want to write a query which shows the remaining capacity which can be purchased by customers (e.g. capacity - purchased) but the query I have written for this excludes any customers who haven't purchased any product. Note I do not want to update the tables, I just want to take a view of the capacity position of our customers.
Example:
Booked Capacity
Customer A = 1000
Customer B = 4000
Customer C = 3000
Product Purchased
Customer B = 1000
At the moment my query gives following result:
Customer B = 3000
What I want it to do is:
Customer A = 1000
Customer B = 3000
Customer C = 3000
Any help would be much appreciated!!!!
Ste
Hi !
I have big problem in putting subtract function in sql query
i have 2 tables
In the first table i make "sum of amount" in each "category_name". In the second table i have 2 columns with the "category_name" and "available_amount".
I make query with join on "category_name" and i want to get like this:
"category_name", "sum of amount", "available_amount","available_amount"-"sum of amount"
I've tried to use "sum of amount" AS a1, "available_amount"-"sum of amount" AS a2 GROUP BY a2
I get "null as boolean in statement" or "column not found a1" errors....
Can anyone help me with this?
Thanks in advance!
i have to make this in a query.
Ok, I always post at the end of the day because by now I've fried my brain thinking and I have simple problems that I can't wrap my mind around.
I need some light shed on this for me,
2 Tables
Table1:
ItemID
NumberSold
Table2:
ItemID
InventoryAmt
I have a Select query, I have a relationship drawn between the ID's. I forget now, Straight line with an arrow head from Table2 to Table1.
All I need is every item in Table 2 to have it's Amt subtracted by NumberSold.
My expression [InventoryAmt] - [NumberSold] works for as long as I have an entry for it in Table1. If there is no record of this item ever being sold it gives a blank value. I would like that value to be the InventoryAmt, since X - 0 = X.
How do I explain to my query that if no record is present in Table1 to Subtract from Table2 just use Table2's value?
This has to be easy and I'm sorry to start a thread over it. It's almost quitting time and I'm banging my head off this, heh.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
Very simple question: I just moved a simple daily rainfall record spreadsheet to my Access gardening database. Fields are date, rainfall in mm, temperature etc. and a single record is added for each day. How do I get the date field to increment automatically to 'previous day's date +1'. (No, I cannot use the shortcut key [Cntrl-;] to insert today's date in the table because records are only entered periodically, several days at a time, from a notebook.) Advice appreciated.
View 12 Replies View RelatedI have financial table which contains list of End of year revenue titles. Since this combo box was suppose to be part of the master table so I made a joint query. Now there is one problem arising. Table and query and even the form is working fine. But in that combo list there are several titles and 3 of them are Assets, Liabilities and Equity. Equity has to be equal to Assets-Liabilities. And the values are being fed into Borrower In house column. I tried everything, but for some reason its not working for me. It wont subtract the values. Any suggestions. I hope I didn't confuse you guys. Thanx in adv.
View 12 Replies View RelatedI have four feilds and im trying to subtract them in a query
This is what the SQL looks liek for two feilds. but i have 4, i was gettign and error when i added other feilds
Format(Val([CFR_ALLOWED])-Val([CFR_DEDUCT]),"0000000000") AS [Form Total],
im trying to subtract these feilds
Here are my feilds
Allowed-deduct-other-Coins-Cob
Much help needed thanks
What i am looking for is i am having a field in query "DEP" which is showing the asset current depreciation as per formula given. I have a problem that the depreciaition is continued on initial cost for all years which i dont want like if it depreciate asset of cost 1000 @ 10% so the figure is 900 for the first year and on second year it should start depreciating from 900 @ 10% so it would be 90 then so on..What i need is to start subtraction last year depreciation
View 4 Replies View RelatedHow can I format the output of subtracting two times as a time value. For instance 10:00 am - 9:00 AM = 1 (where I want to show the output as "1").
View 7 Replies View RelatedI have a database that stores information for lab testing. Each time a tech does a "step" in the test process he logs it in the table, using an input form. There are different categories, for example preparation, testing, analysis, etc, and each of those steps take time. I have the form autopopulate the date and time with NOW() evertime the form is updated. What i want to do is calculate the time it takes to do each in days. I can easily get how many days it was from now since they logged the test, =NOW()-TestDateTime. What I want to do is get the number of days it took to do each step, ie the number of days between each event. Is there a way to do this?
View 3 Replies View RelatedI have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
I have a date/time text field on a form with the General Date format and a combo box next to it that has sequencial times as the row source (IE. 12:45 AM, 1:00 AM, 1:15 AM, 1:30 AM, ETC.) When the user chooses a time in the combo box, I want the time portion of the text box to be updated with the chosen time in the combo. I have tried a few things but cant seem to get it right.
View 4 Replies View Relatedso i created a system to have events booked, and i am trying to check time availability of the event room available, but i dont know what wrong. it either my query or vba code. i have attached the attachment,
View 5 Replies View RelatedHi
I'm on the verge of successfully using a DLookup function (with the help of previous posts) in a query in order to obtain the value (volume1) of the previous record based on a primary field which happens to be a date (iddate).
The following results in sporadic success:
Expr1: (DLookUp("[volume1]","tbldailylog","[iddate]=#" & [iddate]-1 & "#"))
The iddate field is sorted and contains consecutive days.
Any help would be greatly appreciated.
Is there such a calculation in REPORT wherein I can have the Running Difference of several numbers?
Example is several Kilometer reading of a vehicle:
18716
18731
18742
difference of red color numbers: 15
difference of 2nd red color number and blue color numbers: 11
total: 26
I don't even know if my calculation is correct ...
Hi everyone,
I have a form with a date field on it. I would like the date field to automatically enter today's date, which it does now, but also I would like it to retrieve the previous record's date and if it is different from today's date, I want the form to use the previous date as the default value.
Does anyone know of how I would go about this?
Thanks,
Chris
Hi,
Wonder if someone can help please. I'm quite new to Access so please bear with me.
I have a data field in my database consisting of both a date and time.
I then have a form containing two fields where the user can type a 'To' and 'From' date to extract the records that they are interested in. The query behind this uses the 'Between[Enter The Date] And [Enter The Date]' coding.
The problem is that because the field contains a time it doesn't return any records when I run the query.
Can anyone offer a bit of guidance on how I could ignore the time part of the field perhaps by adapting the above.
Many thanks
Chris