Empty Dates.. Why 12:00:00 AM???
Jul 15, 2005
Dear All,
I'm trying something quite simple, adding dates into a table with the aid of an action SQL statement (INsert Into) in VBA.
However, if a date is empty, the value 12:00:00 AM is filled in! I really want that if it a date is set to empty, that noting is added to the table... I can use an if statement though, to fix it, but why does it fill in 12:00:00 AM if a date is empty?? Can you fix it? Or is there no other way?
I use Access 2003.
tnx,
Zurvy :eek:
View Replies
ADVERTISEMENT
May 12, 2014
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
View 1 Replies
View Related
Aug 28, 2013
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies
View Related
Apr 9, 2015
I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
View 4 Replies
View Related
Sep 7, 2006
Hiya-
I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.
One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.
This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.
What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?
Many thanks, Jules.
View 3 Replies
View Related
Jul 8, 2014
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014
table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014
So the answer of the query would be 2,0,4.
Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
View 2 Replies
View Related
Nov 15, 2011
I have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100
Matt 15/5/11 15/9/11 7/7/11 £200
Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Is this possible in access?
View 10 Replies
View Related
Nov 3, 2005
Hi,
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
Example:
StaffID---Start_Date---End_Date
---1-----12/12/2004--14/12/2004
Returns:
StaffID---Leave_Dates
--1-------12/12/2004
--1-------13/12/2004
--1-------14/12/2004
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
Is this possible?
Any help v.much appreciated.
TS
View 3 Replies
View Related
Apr 4, 2012
I have a scenario where the first three rows of date which have dates of 4/1, 4/4/ 4/6 with ndc 5513026701; next six rows that have dates from 4/8 to 4/20 with ndc 5513014801; next three rows that have dates from 4/25, 4/27, 4/29 with ndc 5513026701.
The issue I am having is I do not know how to have separate min/max dates for ndc 5513026701 since when I group by ndc 5513026701 min = 4/1 ; max = 4/29. I need to have min = 4/1 and max = 4/6 for one row and another row of min = 4/25 and max = 4/29.
Any easy way to sequentially create min/max for each ndc 5513026701? I wasn't sure how to verbalize this so I have attached a sample worksheet.....
View 2 Replies
View Related
Oct 18, 2007
Hello World:I have two tables, one called Assets and the other table called Job Sites. the Assets table has several fieldsAssetID - autonumberplus othersthe Job Sites table has two fieldsJobSiteID - autonumberJobSite - text (with about 22 names in it)the I add the JobSite to the Assets table (hiding the JobSiteID) it shows me the expected drop down box but it is BLANK. If I click on any of the BLANK spaces in the drop down, it populates the field. It is behaving as if the font color was white.Any ideas??? I am baffled. :confused: :confused:
View 8 Replies
View Related
Jul 12, 2005
Hello,
I have just created a query so that I may base my report on it. The query ties together 4 tables. I only included the fields that will be in my report. I created the report using the wizard and used my new query as the source but it does not want to work. I tried opening the query and it was empty even though I have entries in the database.
ANY IDEAS :confused:
View 2 Replies
View Related
Jul 27, 2005
I have a query that appends records to a table with a constant numer of fields. so sometimes I have fields with no data. is there any way to create query which selects only fields with data.
thanks in advance
View 1 Replies
View Related
Jul 7, 2006
I am trying to make a query with two tables. Each row consist of a student ID, their name and all their personal information. Both tables are exactly the same. But one table is a link table. That link table gets update every so often from an outside program and the other table is not linked. I want to be able to run a query that updates certain fields called "address changes". I have figured out how to find the updates, but I am having a problem with a field that is blank. If I have a field in the non-linked table that is empty but there is information in the linked table, it does not see it. Is there a way to get around this empty field problem?
View 3 Replies
View Related
Apr 24, 2008
I have been going back in a db to the beginning of time and restructuring...I have a field sales person if its null I need to assign a value of 100 to another field representativeid, I have been using the update query to accomplish the update until now. I've tried is null and "" to get this query to update, any idea?
View 4 Replies
View Related
Mar 14, 2006
I have an Input form that does not clear the fields after I hit the Save button. Data Entry is set to YES. If I use the navigation bar, there is no problem, but I would like to have the fields empty after hitting the Save Button, so that the navigation bar can be removed. What is wrong?
View 4 Replies
View Related
May 30, 2006
Hi guys, here is my new problem.I have a form which conatins a subform that shows info from a query.I created a query which shows records from table history when the field comment is like *code 1.the criteria in the query, in the comment field is: [forms]![edititem]![vconcate]In the edititem form i have a hidden field called vconcate which receives the value as follows:me.vconcate= "*code " & me.winecode (me.winecode is integer)after assigning the value to vconcate i display the vconcate value and shows*code 1, as it should be. However, the subform doesnt displays any record.I also tryed to insert the query in the editform, but nothing.if i run the query from the query window, and fill the value as *code 1, it works.here is more info:when the edititem is open, the user has to select a wine code from a combo box. After this selection the values are assigned to me.winecode and the me.vconcate. Because when i open the form edititem these codes are null or empty the subform is empty. Is there any way to refresh the subform via code?any suggestion?
View 1 Replies
View Related
Jun 6, 2006
Hey guys......simple problem
I have a form with 3 combo boxes.
The 1st combo, called winecode, gets the values from the following sql:
SELECT [winecode] FROM wine WHERE winedesable=0;
it works.....
The second combo box, called suppname, gets the values from another select, but depending on the previous combo:
SELECT DISTINCT [supplier].[suppname] FROM supplier, suppwine WHERE [supplier].[suppnumber]=[suppwine].[suppnumber] And [suppwine].[winecode]=[Forms]![addpop]![winecode];
it works......
The 3 rd combo, called winesubcat, gets the values from a query which takes the selected value from the previous 2 combos. The query works by it self . However, i added the query in the data tab and open the form. then i selected values from the first 2 combos and there is no value in the 3rd combo...... any idea??
thx max.
View 1 Replies
View Related
Oct 24, 2006
When I click a button I have code (below) that checks to see if my Combo boxes are NULL (empty), and if they are it’ll prompt the user to “fill in all fields”. Right now it includes all of the combo boxes on my form. Although, I want it to only include 3 combo boxes (I have 5 on my form). How could I do this, lets say if my combo box names are cbo1, cbo2, cbo3 ?
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acComboBox
If ctl.Value & "" = "" Then
MsgBox "You must fill in all data fields."
Exit Sub
End If
End Select
Next
View 3 Replies
View Related
Apr 15, 2005
In a previous post someone mentioned a difference between Null and Empty. Is Null is not pulling the nulls but I know those fields are empty. Can someone tell me how to pull in these empty fields. for my calculation I need to get Companies that showed revenue after and didn't show revenue once their contract started.
Thank you
View 2 Replies
View Related
Jul 13, 2005
Hello, I have writen a piece of code:
Code:DoCmd.RunSQL "SELECT tblTrades.tradeID, tblTrades.TradeConfirm, tblTrades.Laurent FROM tblTrades WHERE (((tblTrades.TradeConfirm)=No) AND ((tblTrades.Laurent)=Yes));"
How do I check to see if it returns any results?
View 2 Replies
View Related
Oct 3, 2005
Help.
I can seem to find a way to test if the SQL for my record set returns value. I've tried using the if statement with .BOF and .EOF. When there are no records returned for the criteria, there should be a way to test it with IF. I can't seem to figure it out. Using the record count does not work because it also returns at least one record. Help.
Here is the code
Dim rsContacts As ADODB.Recordset
Set rsContacts = New ADODB.Recordset
SQLStr = "SELECT FormatPercent(((43200-Sum(DateDiff('n',[tblOutageDetail.StartTime],[tblOutageDetail.endtime])))/43200),2) AS ElapsedTime FROM (qryOutageTotalMin INNER JOIN tblOutageData ON qryOutageTotalMin.Outage = tblOutageData.Outage) INNER JOIN tblOutageDetail ON tblOutageData.Outage = tblOutageDetail.Outage WHERE (((tblOutageData.System)='PARK PRIMARY') AND ((tblOutageDetail.StartTime)>=Date()-30) AND ((tblOutageDetail.OtgCat)=1))"
With rsContacts
.Open SQLStr, CurrentProject.Connection, adOpenStatic, adLockReadOnly
End With
'Me!btnparkpri.Caption = CStr(rsContacts!ElapsedTime)
'Set rsContacts = Nothing
If rsContacts.RecordCount = 1 Then
Me!btnparkpri.Caption = CStr(rsContacts!ElapsedTime)
End If
If Me!btnparkpri.BackColor = 255 Or Me!btnparkpri.BackColor = 32768 Then
Me!btnparkpri.ForeColor = 16777215
End If
Set rsContacts = Nothing
View 2 Replies
View Related
Jul 7, 2006
I am trying to make a query with two tables. Each row consist of a student ID, their name and all their personal information. Both tables are exactly the same. But one table is a link table. That link table gets update every so often from an outside program and the other table is not linked. I want to be able to run a query that updates certain fields called "address changes". I have figured out how to find the updates, but I am having a problem with a field that is blank. If I have a field in the non-linked table that is empty but there is information in the linked table, it does not see it. Is there a way to get around this empty field problem?
Thanks
View 2 Replies
View Related
May 9, 2005
Hello visitors,
I have a report base on a select query but in the report i get the following:
page 1 = record 1
page 2, page 3, page 4 = are empty (nothing to see on the pages)
page 5 = record 2
page 6, page 7, page 8 = are empty (nothing to see on the pages)
page 9 = record 3
...........etc...
So there are three empty pages between every two pages(records).
I have checked in the settings of the report and all properties but cannot find the solution.
Can any one help me to solve this ?
Thanks in advance
View 3 Replies
View Related
Dec 21, 2005
i wonder if anybody can give me a hand here...
i have an SQL insert query as follows:
DoCmd.RunSQL "INSERT INTO Despatches (" & _
"[SupplierRef], " & _
"[DespDate], " & _
"[TimeIn], " & _
"[TimeOut], " & _
"[QtyLoaded], " & _
"[Shift], " & _
"[DocRef], " & _
"[TrailerRef], " & _
"[SealRef], " & _
"[Comments], " & _
"[TeamLeader]) " & _
"VALUES ('" & Supplier & "'," & _
date1 & "," & _
time1 & "," & _
time2 & "," & _
DespQty_1 & ",'" & _
shift1 & "','" & _
docref_1 & "','" & _
trailer_1 & "','" & _
seal_1 & "','" & _
comments_1 & "','" & _
TeamLeader.Value & "');"
This is working fine as long as I input the values for all the fields...when some values are missing, I get the error that Access can't append the query due to validation rule violations...got no clue what to do !
plss help!:o
View 4 Replies
View Related
Jan 27, 2007
Can anyone help me with this small problem?
Using expression builder what expression do I use to not display the filed in my query if the field is formatted as currency and is blank (showing £00.00)
View 6 Replies
View Related
May 31, 2007
I have a form with a subform on it. My subform gets populated by a lookup. On my main form I have an unbound (cboLotIDLU) lookup field and also a text box (LotID) that shows the chosen record. When I open the form my cboLotIDLU is blank but my LotID has an ID in it so the info for that ID is displayed on my subform. I would like everything to open up blank. I've looked around and I can't see where that first record is coming from. How do I get it so everything comes up empty?
View 14 Replies
View Related
