Missing Dates
Mar 1, 2006
Hi,
I have a list of date fields which are not required. However, I want to disable some other fields when the date field is blank. I can't figure out how to do this. I can do it when I use a dummy date as the default, but I have a lot of these, and want to know how to tell it to skip the field.
I was planning on doing something like in the Visual Basic Code attatched at the exit event:
If date = Null then
me![follow].enabled = false
elseif date not null then
me![follow].enabled = true
me![follow].setfocus
As you can guess, I am nothte most proficient Access user ever:)
Thanks,
ingrid
View Replies
ADVERTISEMENT
Dec 6, 2006
i have a report that opens from a query that pulls from 8 different tables using the date as the primary key for each table. I want to be able to open my query without having to input a blank record for each date in each table. Basically I don't use every table everyday. Sometimes I just use 3-4 tables and others I will use all 8.
View 1 Replies
View Related
Oct 7, 2005
Hi all
This is an ongoing problem I have had for 4 weeks now.
I have made a a system thats acts like a clock In/clock out Out system.
the structure is somthing like this
ID
Username
tblDailyLog
TimeIn
MorningBreakOut
MorningBreakOut
LunchOut
LunchIn
AfternoonOut
AfternoonIn
TimeOut
All fields apart from ID (autonumber) and username (String*255) are Date field (there are a few others like DateOfTimesheet etc but they arnt important here)
When a user arrives in the morning they make a record which they use for the day
They then have a form with a whole bunch of buttons which simply updates the correct field. For example they click the "Sign in for the Day" button and it updates the correct field with the current time.
Everything was going fine until people noticed that every now and again a sign in time dissapeared.
I have hacked myself to death trying to solve this problem but still the updates go Astray.
Now each time a time is updated the process goes somthing like this
1. the user opens their timesheet for the day (the RS is SNAPSHOT and no locks)
2. User Hits a sign in/out button
3. The record source is changed to "" and all buttons hidden (to ensure the record isnt locked and to make sure you dont do two things at once)
3. The table is updated with the new time (using some dynamic SQL)
4. The table is repeatadly checked using a DO loop to make sure the the correct time went in.
5. when the returned time value of the field matches the varaible used to update it, the form is returned to normal and the user carries on his/her merry way (if it never matches the screen should crash but this never happens).
6. A New record is added to another table called "tblbugfixinglog" which records which field was updated and when. This is so that I have two records in two different ways (figured if one went astray I could pull it back off the other)
7. Another new record is added to yet another table called tblSQLRecord, which simply logs all .RUNSQL statements that are executed.
I thought that the two extra tables (and the check that the record had been updated) would help me track down where the records are going missing, but this isnt the case.
Now it appears that some records arnt being added to tblBugFixingLog and to tblSQLRecord either and some of these tables are getting quite a few #ERROR's in them..
None of the tables are related to any other and i've no idea how #ERROR lines are appearing in a table that has 1 function... to recieve new records ... no editing, no viewing, no deleting.
Does anyone have any idea how these updates/inserts can go missing or create #ERRORs.
I've built plenty of Databases in my time and have never come across this.
__________________________________________________ ______________
This is the function I use to add a record to tblBugfixingLog and tblSQLRecord
Private Sub AddBugLog(ByVal TimesheetNumber As Long, ByVal FieldUpdating As String, ByVal NewFieldValue)
Dim TempSQL As String
TempSQL = "INSERT INTO tblBugFixingLog (TimeAndDateOfEntrySERVER,TimeAndDateOfEntryPC,Fie ldUpdated,NewEntry,UserID,TimesheetNumber,Computer AssetNo) VALUES (" & _
"#" & Format(ServerGetTime(Environ$("LOGONSERVER"))) & "#," & _
"#" & Now & "#," & _
"'" & FieldUpdating & "'," & _
"'" & NewFieldValue & "'," & _
"'" & GetNTUser & "'," & _
"'" & TimesheetNumber & "'," & _
"'" & fOSMachineName & "')"
' MsgBox TempSQL
DoCmd.RunSQL "INSERT INTO tblSQLRecord (Username,DateAndTime,Screen,TheSQL) VALUES('" & LoginInfo.sUsername & "','" & CStr(Now) & "','Add Bug Log function','" & CleanData(TempSQL) & "')", False
'CleanData is a function that removes ' and " from the SQL string so i can easily add the SQL string into the table
DoCmd.RunSQL TempSQL, False
End Sub
Public Function CleanData(ByVal DataToClean As String)
Dim TempData As String
Dim i As Integer
TempData = ""
For i = 1 To Len(DataToClean)
Select Case Mid(DataToClean, i, 1)
Case "'"
TempData = TempData & "`"
Case """"
TempData = TempData & "`"
Case Else
TempData = TempData & Mid(DataToClean, i, 1)
End Select
Next i
CleanData = TempData
End Function
__________________________________________________ ____
I have no idea how this can create #ERROR lines in the table when it is just added to and nothing else.
Does anyone have any clue to what may be happening here.
(Oh yeah and no matter how hard I try, I can't replicate the problem.... works for me every time no matter how harse I am to it!)
Please save what little hair I have left and give me some hope
Cheers
Homer
View 1 Replies
View Related
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
Aug 18, 2014
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Code:
MemberIDBoats
5882Opossum(78-80) (87-89) Otter(80-84) Opportune(91-93) Turbulent(97-00).
5883Astute Auriga Aeneas Affray Amphion
2407H34 O10 Porpoise Trenchant Tapir.
I want to create a table as follows;
Code:
MemberIDBoatFromTo
5882Oppossum19781980
5882Oppossum19871989
5882Otter 19801984
5882Opportune19911993
5882Turbulent19972000
5883Astute
5883Auriga
5883Aeneas
5883Affray
5883Amphion
Etc.
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
View 14 Replies
View Related
Sep 19, 2007
When I run this Query without the WHERE statement/Clause- it returns the accurate 985 records. However, when I include the criteria with the WHERE clause (it takes a range of +/- 10% of the Square Footage size into consideration), I lose 15 records, and only get 970 results. When I increase the range to +/- 15% in that field, it gives me 7 more records back.
Obviously it is dropping out records that do not meet the criteria for that field- how do I prevent that? I guess I would like it to display a ZERO or something, but still display the entire 985 records. What do I need to include to do that? Here's the SQL-
SELECT tbAct.MLS, tbAct.Status, tbAct.Address, tbAct.City, tbAct.Area, tbAct.SqFt, tbAct.Level, Avg(tbSold.SoldPrice) AS AvgOfSoldPrice, Count(tbSold.SoldPrice) AS CountOfSoldPrice
FROM tbListing AS tbAct, tbListing AS tbSold
WHERE (((tbSold.SqFt) Between ([tbAct].[SqFt]+([tbAct].[SqFt]/100*10)) And ([tbAct].[SqFt]-([tbAct].[SqFt]/100*10))))
GROUP BY tbAct.MLS, tbAct.Status, tbAct.Address, tbAct.City, tbAct.Area, tbAct.SqFt, tbAct.Level, tbSold.Status, tbSold.Area, tbSold.Level
HAVING (((tbAct.Status)="ACT") AND ((tbAct.City)="TEMPE") AND ((tbSold.Status)="CLOSD") AND ((tbSold.Area)=[tbAct].[Area]) AND ((tbSold.Level)=[tbAct].[Level]));
View 2 Replies
View Related
Jun 3, 2005
I hope someone can help me with this. I figure I'm missing one line of code but can't figure it out. I have 3 tables - tblGeneral (which is my main table), tblMDLink, and tblMD. The primary key in tbl General is FileNo. The primary key in tblMDLink is an autonumber and the primary key in tblMD is also an autonumber called MDID. tblMDLink is just a linking table containing the fields Fileno and MDID and uses an autonumber as its primary key. It contains some other fields that have nothing to do with this. I have a query called qryMDLink which contains all the fields of tblMDLink together with a calculated field calle full name which just combines the last name and first name fields of tblMD. Now I come to my form (which is actually a subfrom) called subfrmIMEDoctor. The purpose of the fomr is to allow users to add the names of various doctors to various files. The record source of the form is qryMDLink. The form also contains a FileNo field which is added programatically. The field has a drop down combo box with its record souce set to MDID. The drop down box has two columns with the first (not visible to the user) set to MDID and the second set to Full Name. Here's the problem - it won't let me enter data. Whenver I click on the box I get the message - "You cannot add or change a record because a related record is required in table tblIMEDoctor". By the way, I did set up one to many relationships beteen tblGeneral & tblIMELink and between tblIMEDoctor and tblIMELink. Any help would be greatly appresciated. Thanks,
Tom Gorton
View 1 Replies
View Related
Jun 13, 2005
I have a relatively large database 65M, 42K records.
For some reason I seemed to have lost most (not all) of the queries I have written. However, if I go to file>database properties>contents, I can see all my queries there.
I certainly did not delete them. Any I idea of what’s going on ? I'm, using Acess 2000 on win2K. Might this be a resources / memory related issue?
Thanks
Joe
View 1 Replies
View Related
Aug 17, 2005
Hi,
I have build a database which work perfectly on most computers. But one or two for the computer are error with the Left, Right and Date SQL function. Is it simplely miss for the install or it a problem with my code?
Thanks
View 2 Replies
View Related
Dec 20, 2005
I am running Access 2000. The control wizard is not available. It does not come up when I add a command button for example. How do I get it turned on?
View 6 Replies
View Related
Jan 10, 2006
Hi,
when designing reports or forms, when I pressing the
toolbox icon, it would depress, but no toolbox was visible. I tried
resetting the toolbox (through view/toolbars) and I set all of the
settings back to default. I even uninstalled and reinstalled MS
Access several times but no luck.
Any help please.
Gunawan.
View 3 Replies
View Related
Feb 14, 2006
Recently I lost the the functionality of the toolbox then shortly after I lost the toolbox icon completely.
The only way to use the features was to create an new toolbar.
System Restore finally fixed the problem.
This has happened twice on me with the 200 and 2003 versions.
Is it me? and is there an easier way to sort this. Reinstalling Access never cured the problem either.
Ta
View 5 Replies
View Related
Feb 17, 2006
Houston, I need some answers.
I don’t have one of those MSysObjects. Now how do I get one and how do I populate it?
View 1 Replies
View Related
Sep 15, 2006
I have a database that has just been split. In the back end, I don't see the tables. I have gone to Tools>Options>View and found that Show Hidden Objects is checked. When I go to the front end, the links to the tables show up and I can open them and see the data. When I check the Linked Table Manager, I see that the path for the links is leading to the back end database.
I also noticed that on the bottom of the screen, the buttons for the front end show the name of the front end database and have blue page-like icons, but the button for the back end has only the pink key icon. Also, when I try to link another mdb to these tables, the window for selecting the table for linking is empty. I also notice that suddenly, any time I close an Access database, it goes through a compact and repair cycle. This just started happening today. I'm fairly sure that yesterday I could see the tables in the back end.
Oh, yes. Just remembered one other thing. Not everyone here has that location on the network mapped to the same drive. In order to accomodate everyone, the linked table manager was set up with the complete path to the folder:
\companyname.comfolderlevel1folderlevel2folder level3...ackend.mdb instead of
J:.....
Using Windows XP and Office 2003
There is no verticle scroll bar as was mentioned in another post I found when doing a search on "Hidden Objects".
Any ideas where I should try next?
View 4 Replies
View Related
Dec 20, 2006
Hi,
i've developed a program in access 2003, but now i'm on access 2000 and seems that one / more library is/are missing..
i've this part of code
Private Sub List0_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim strOrd As String, strSql As String
If Button = 1 Then
If Y <= 225 Then
If Right$(List0.RowSource, 5) = " Asc;" Then
strOrd = " Desc;"
Else
strOrd = " Asc;"
End If
strSql = "SELECT cliente.CLI_ID, cliente.CLI_TITOLO, cliente.Cognome, cliente.Nome, cliente.Indirizzo, cliente.Cap, cliente.[Citta'], cliente.Regione, cliente.Nazione, cliente.CLI_SOCIO FROM cliente WHERE (((cliente.CLI_SOCIO)=Yes)) ORDER BY "
Select Case X
and this is the screen with the libraries currently installed ..
http://img246.imageshack.us/img246/7383/libreriaum5.th.jpg (http://img246.imageshack.us/my.php?image=libreriaum5.jpg)
i was wondering which one is missing....i'm quite far from my laptop right now..
thanks in advance
bye
View 2 Replies
View Related
Jun 26, 2007
Alot of my databases entries are missing. today i went on MS access 2007 and it said "security risk" so i went to options and enabled marcros. but when i get on to my database there is only one entry! but i know the information is there. the file is 1700 mb. what should i do?
View 1 Replies
View Related
Nov 13, 2007
I make that ERD :-
http://www.imagehosting.com/out.php/i1365582_erd.PNG
is there any suggest to add or remove fields or tables ?
I attached the file.
and thanks.
View 3 Replies
View Related
Feb 7, 2008
I have a weird situation and I was wondering if anyone has had an issue with this. I have a record missing from a table. Normal Users are not allowed to delete records from the Form view. There is a blank record where the record should be. The auto number counts from 37, 38, 40. Record 39 is missing and there are blanks where it should be. The autonumber field is my Primary Key and it is blank just like the rest of the fields in the record. I know that the record existed at one time because one of the DB users has a report referencing that record. Any Ideas?
Thanks for the help,
Keith
View 1 Replies
View Related
Jan 13, 2006
I have what seems to be a fairly simple question.
There are two tables in my database. A and B. Each table has the same criteria: Name, Number, and Date.
I have created a formula in my query which will add the Number fields together when they both have the same name. My problem is that when there is not a row for a particular name on table B, the calculation does not list that row in the results.
I would like for my calculation to insert a zero in the space and add Number.
I have tried the Nz function, but this results in a missing name and date and just the numbers are shown.
How do I get the results to show a row where the name is only on one field and still make sure that it places a zero in its spot and add the zero and the other number together based on the name.
Thank you in advance for any assistance.
Shawn
View 4 Replies
View Related
