Bizarre Reocurrence Of Time Data In Table
Jan 26, 2005
Hi all,
Been having this problem for a long time.
Everytime my function is being called, a new tuple gets inserted into Table "Stats" containing the below mentioned data.
sSQL = "SELECT * FROM Stats WHERE UserID = '"&sUserID&"'"
rs.Open sSQL,,,adCmdTable
'Save the data
rs.AddNew
rs("JaID") = lIdJa
rs("JsID") = lIdJs
rs("LanID") = lIdLan
rs("ResID") = lIdRes
rs("WsID") = lIdWs
rs("Date") = Date
rs("Time") = Time
rs("IP") = sIP
rs("UserID") = sUserID
rs.Update
Some of the weird data i got from Table "Stats"
1/25/2005 12:45:59 aaaa bbbb cccc dddd ..... row1
1/25/2005 12:46:24 aaaa bbbb cccc dddd ..... row2
1/25/2005 12:46:29 aaaa bbbb cccc dddd ..... row3
1/25/2005 12:46:35 aaaa bbbb cccc dddd ..... row4
1/25/2005 12:46:40 aaaa bbbb cccc dddd ..... row5
1/25/2005 12:46:40 aaaa bbbb cccc dddd ..... row6
1/25/2005 12:46:40 aaaa bbbb cccc dddd ..... row7
1/25/2005 12:52:51 aaaa bbbb cccc dddd ..... row8
Each time a person click on my site, one tuple will be inserted into the Table "Stats".
Time which is one of the column of the Table "Stats" is inserted into the table each time the function is called
The puzzling thing is how is it possible for the time to be the same for row 5,6 & 7.
This happens very often and i have totally no explantion for it. I tried changing the time format, date format, however i always get this.
Please advise.
Thanks a lot
View Replies
ADVERTISEMENT
Oct 4, 2007
Hi,
First time on here, so hello all!
I have a very strange request from a client who wants to do something simple that they can't because of poor database design (not mine!) . I can't think of an elegant solution so I'm wondering if anyone on here can?
They have multiple tables containing address information, for example they have one containing information about certain buildings. They can use the softcopy of the database to retrieve information about the building but for backup they now need a hardcopy.
Now part of the building information includes the alternative names for buildings (for example "City Museum" and "Art Muesum" might be different names for teh same building). The hard copy needs to have a line entry for both names (proper and alternative) and this is where the difficulty lies.
Using a simple example the table might be:
colour fruit
red apple
orange
yellow banana
The output I would need in the report is therefore:
Red (Apple)
Apple (Red)
Orange ()
Yellow (banana)
Banana (Yellow)
Now I may be being dense but I can't see how I can do this! All help very gratefully recieved :)
View 1 Replies
View Related
Aug 25, 2007
I built my wife a database to use for her work that has worked fine for a good while now. Just yesterday, one of the make table queries began to act peculiar. Suddenly, whenever you try to run it it gives the following error:
"The Microsoft Jet database engine has stopped the process because you and another user are attempting to change the same data at the same time."
This is on a computer at our home so obviously no one else is trying to access it. I have checked and there are no other instances of the database running (I've even rebooted to be sure). I have checked when it was shut down and didn't see a locking file associated with the database. It is a single make table query feeding directly from another table.
As I said, it was working fine for a good while. I'm out of ideas so can anyone help????:confused:
View 3 Replies
View Related
Sep 12, 2014
How to make a form open with data from a table based on a date and time in the/a table?
View 7 Replies
View Related
May 27, 2005
Hi All!
This to me seems absolutely bizzare! It happens in both Access 97 and Access 2003 so I know its not a version issue.
I have a Access database with a link table to a spreadsheet. I have written a query which displays data from this link table which filters out blank rows, the spreadsheet header etc and just leaves me with the data I want to import which first time I ran it was perfect and exactly what I wanted. However, it turns out that this query only works intermittently. If I run it subsequently I get a "numeric field overflow" error message. But if I go into the query design and resave it without making any changes it will then run again!!! Running it thereafter will bring back up the error message until I resave it again!!! Why is this? Any ideas anyone? I dont want to have to keep resaving it to run it.
Actually thinking about it unless I could of course resave it using code???
Regards,
Dalien51
View 3 Replies
View Related
May 25, 2005
Greetings all,
I'll try to make this as simple as possible, but please ask for clarification if I'm unclear:
*I have one pass-through query to Informix that pulls all available class schedule information.
*I have another local query that uses information in a local table to restrict that query to a particular year and semester, chosen by the user.
*I have a function TimeConv that takes the numeric value used in the Informix db to store times (e.g., "1300") and converts it into a real time value (e.g., "1:00:00 PM").
The problem is this:
While the TimeConv function by itself always works in the local query, if I try to perform any comparisons on it, I get the error message "Invalid Use of Null". There are no null records in the restricted data. If I put the semester and year restriction directly into the pass-through query instead of the local query, the comparison operations work just fine. (But it's important that the user be able to change this without editing the SQL directly.) Only when that restriction is in the local query do I get the error message.
I've tried several ways of filtering out Null values from both queries, and it doesn't seem to make any difference. If I Nz() the fields before passing them to the TimeConv function, I instead get the error "Invalid procedure or argument call", I believe because it insists on reading "0000" as "0", which is too few digits for the function to work.
I get the same results if I try to run it through yet a third (local) query.
Relevant code and SQL below. I'm really stumped on this one. Thanks in advance for any suggestions!
Pass-through query (qryPassJoin1):
SELECT t3.days, t3.room, t2.crs_no, t3.bldg, t3.mtg_no, t1.sec_no, t3.beg_date,
t3.end_date, t3.beg_tm, t3.end_tm, t9.sex, t5.abbr_name, t1.yr, t1.sess
FROM informix.sec_rec t1, informix.crs_rec t2, informix.acad_cal_rec t7,
outer (informix.secmtg_rec t8, outer informix.mtg_rec t3), outer
informix.dept_table t6, outer informix.schd_comment_rec t4, outer
(informix.fac_rec t5, outer informix.profile_rec t9)
WHERE t1.cat="UG93" and t1.crs_no=t2.crs_no and t1.cat=t2.cat and
t1.crs_no=t8.crs_no and t1.cat=t8.cat and t1.yr=t8.yr and t1.sess=t8.sess and
t1.sec_no=t8.sec_no and t8.mtg_no=t3.mtg_no and t1.crs_no=t4.crs_no and t1.cat=t4.cat
and t1.yr=t4.yr and t1.sess=t4.sess and t1.sec_no=t4.sec_no and t1.fac_id=t5.id
and t2.crs_no NOT IN
("JFESSA", "FRESSA", "SOESSA", "JRESSA", "MAESSA", "MAORAL", "DEPO", "DEPO2", "SRESSA", "SRORAL")
and t2.dept=t6.dept and t7.prog=t2.prog and t7.sess=t1.sess and t7.yr=t1.yr
and t5.id=t9.id;
If I add this criteria, functionally equivalent to the first WHERE statement in the local query below, it all works fine:
AND t1.yr=2005 AND t1.sess="SU"
Local Query (qryCatalog):
SELECT qpj1.days, qpj1.room, qpj1.crs_no, qpj1.bldg, qpj1.mtg_no, qpj1.sec_no,
qpj1.beg_date, qpj1.end_date, qpj1.beg_tm, qpj1.end_tm, qpj1.sex,
qpj1.abbr_name, DateDiff('h',TimeConv([beg_tm]),TimeConv([end_tm])) AS NoLongLab
FROM qryPassJoin1 AS qpj1, tblSettings
WHERE (((qpj1.beg_tm)>0) AND ((CInt([yr]))=CInt([tblSettings].[catyear])) AND
((qpj1.sess)=[tblsettings].[catsess]));
The comparison criterion I would like to add here is:
AND ((DateDiff('h',TimeConv([beg_tm]),TimeConv([end_tm])))<=2)
Function TimeConv:
Public Function timeconv(numtime As Variant) As Date
timeconv = CDate(Left([numtime], (Len([numtime]) - 2)) & ":" & Right([numtime], 2))
End Function
View 3 Replies
View Related
Jan 30, 2006
I have a form that opened perfectly before from a click event with a standard link criteria lookup function, and...it's suddenly stopped working!
Before the form opens I get a small query box asking: Forms!support!fault ref
In other words it's asking me to enter the value it should've got from the link criteria lookup. Whatever I enter, the form then loads with the correct record.
Is there a known Access corruption issue, or does anyone have any ideas?
View 1 Replies
View Related
Aug 14, 2007
This is driving me crazy! I have some code that has a list of items in a listview. When I right click it shows a pop-up right click menu with a macro. Then I select an item in the list that says Rename. After ensuring a single element is selected the StartLabelEdit method is called on my list view. Then I can begin editing. Normally this works fine. However, if I first double click a list item. Then I enter my double click list view event handler and from there I open a form. Later if I close that form and then try right-clicking and selecting rename. This time after StartLabelEdit is called, the BeforeLabelEdit event is never called. The whole selected line blinks momentarily and then wherever my cursor is at that moment every item between it and the originally selected item becomes selected. If I move my mouse around the selection changes to be between the originally selected item and the one my mouse is over as it moves. It's as though I had clicked the left mouse button down but didn't let go of it and then started moving the mouse around. If I left click and release again it seems to get out of this mode, but I am still not able to ever manually invoke the StartLabelEdit method after this point. If I close the form and re-open it, it works fine. (I've tried calling refresh on the list view but this had no effect.)
Here's the really weird part. If instead of double-clicking to open a form, I right click and select a menu option to call the exact samed double click event method. Everything works fine as you would expect. Then I tried another test. I made the double click method call another method to open the form. When I did this and tried the same alternate way of opening a form, it did not work and the same original error occurred again. This weird behavior is bizarre and I have no real idea how to troubleshoot it any further.
:mad:
Has anyone ever seen or heard of something like this where StartLabelEdit goes haywire? Please help me if you can.
Thanks...
-Will
View 1 Replies
View Related
Mar 4, 2014
I have a simple data entry form where I can key the "row", "COLUMN", SPACE "a" and space "B" onto an access 2010 table. Is there a way that if I key the same four fields, e.g., A5AB that I get a message back before updating telling me that combination already exists?
I do generate a calculated data field, which in this case would be "a5ab" and which is displayed on report screens. Could the value of this calculated field be stored on the Table and be used to prevent the same value from again be entered on the Table?
View 2 Replies
View Related
May 6, 2015
how to be able to enter time in access form the same way as if would be an access table (1p = 1:00 PM; 1.25 = 1:25 AM etc)
View 1 Replies
View Related
May 23, 2006
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
This screen shot may help:
View 2 Replies
View Related
May 23, 2006
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still short time but the format is in AM/PM or medium time.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
View 1 Replies
View Related
Nov 10, 2006
I have a file that collects the time data of the shop employees. It records time in (start of day), time out (lunch start), time in (lunch end) and time out (end of day).
Sample of the table:
In/OutEmployee IDDateTime
0316 11/2/20067:00:34 AM
01244 11/2/20067:05:00 AM
1316 11/2/200612:00:43 PM
11244 11/2/200612:01:21 PM
0316 11/2/200612:30:43 PM
01244 11/2/200612:34:25 PM
1316 11/2/20063:30:46 PM
11244 11/2/20063:34:34 PM
0 = In, 1 = Out
How can I calculate the total hours worked per day(First In + First Out + (Second In + Second Out).
I appreciate any help.
View 6 Replies
View Related
Jun 27, 2005
Hi, I have a form which is made up for 3 tables and I am trying to create an append query for each one in order to keep records of data before it is updated. The append queries seem to work but they append all data rather than just one selected record. I know I will next need to create a macro which can be used each time a record has been updated and a copy is sent to the archive. Can anyone help me with this, or have any useful suggestions?
Thanks.
View 2 Replies
View Related
May 3, 2006
Hi - I'm working on a query, and can't seem to get it to work correctly. I have a table which contains 2 social security number fields - one for a dependent/employee with benefits, and one for the originally insured (for example, if an employee passed away, the dependent would be on the file, and the originally insured data would be populated with the original employees data). There are also cases where the employee and dependent exist in the table at the same time. I need to check the table, record by record, to identify any cases where the two SSN's are the same in any given record. The query is currently finding an employee's SSN on their record, and also finding it in the originally insured field of the dependent's record and showing it as an error. I only need to know if they occur for the same record, not for another record in the table. (I hope this makes sense!!!)
Is there any way that I can do this??? Any help would be greatly appreciated!!!!
View 4 Replies
View Related
Mar 25, 2008
Hello,
I've faced a - for me - tricky problem when I've created a query from code. I have an MS SQL backend and in it there is a table which has a long time format field. I want to make an sql query where the condition is a time data. Unfortunately when I enter a time which is surely in the table, there is no result of the query. I've tried to solve it on several ways, time formatting, etc., but I had always the same result, to wit no result.
Does anyone idea, how could I solve it?
Any tips are appreciated very much!
Many thanks in advance!
VoiD:confused:
View 1 Replies
View Related
Apr 4, 2005
I have a form that asks 2 questions. Date and Account. I need to check a table to see if the combination of both date and account have not been used. Date is normally today, can be yesterday. Account has a drop down menu with 6 choices. If both the date and account have been used then there should be an error shown to that effect. If not used, then proceed on to the next form for data entry.
Can this be done? And how?
View 1 Replies
View Related
Jan 31, 2006
Hi,
I am using this code below to retrieve the last updated time on the tablea.
lbl_DataLastUpdated.Caption = "Data last updated: - " & Format(CurrentDb.TableDefs("ATM_Info").LastUpdated, "dd/mm/yyyy hh:mm")
This works but the problem i have is, when i add an entry via a form or do anything else via a form. This does not get updated?. I check the properties of the table and that isn't either.
When i then modify something manually in the table (go in and change it). It then updates.
Is there something im doing wrong or is there a better way to check the last updated.
Thanks
k0r54
View 3 Replies
View Related
Jan 13, 2007
While much of the discussions here are out of my league, this may just be the place to finally get some help.
I have a hobby weather station and I am also into gardening. Over the years I have accumulated a fair amount of data/ observations which are currently in assorted Excel spreadsheets & are becoming more an more difficult to maintain.
So, I decided to move the various Excel tables into MS Access. While I was able to draft a entity-relationship diagram for my horticultural data, I am at a total loss when it comes to weather/ climate data which are primarily time-dependent. With other words, I cannot find any references that explain how to design pairs of foreign and primary key for linking datasheets that contain time varying data such as various weather and climate data.
I have ordered dozens of books through the local library. Every book presents exactly the same example: customers and orders/sales. This example is fine when you have a myriad of interdependencies in your data (and proved useful for my horticultural data). But, my weather data are more or less independent of one another; the only thing they have in common is the time/date when I make the observations.
Do I have to create dozens of surrogate keys and add a ton of redundant data ?? I hope not !
View 14 Replies
View Related
May 30, 2006
The Database is coming along nicely now, but in need of guidice with this one:
On the main table of the contacts I have what you could call a date of 01/01/06 and a combo box next or below that that I select what risk levels to asign the user.
Each user would have a no of year review based on the risk level:
High = 1 Year
Med = 2 Years
Low = 3 Years.
There is another date box that is blank at the moment, what I am looking for query wise or another way is on change/update the risk level is to add the start start in this case 01/01/06 (if user is Med Risk - then add 2 years) and adding risk level giving me a new date of 01/01/08 in the blank date box (this option will make the main table update.
Can anyone point me in the right direction for this one?
Thanks
Mike
View 1 Replies
View Related
Oct 18, 2006
I am new to Access and would be very grateful for some adivce on setting up a query.
The data in the table below comes from a photon counter. Both fields are numbers although TIME actually represents seconds.
I need to be able to group the DATA field by arbitrary TIME periods. e.g. to divide the table up into bins of, say, 8.7 seconds length, showing the total accumulated in the DATA field for each successive period of 8.7 seconds.
The tables are about 300,000 records long.
Any help would be much appreciated.
Thanks,
TIME - DATA
285748175.864557 - 100
285748175.874556 - 100
285748175.884556 - 0
285748175.894557 - 100
285748175.904557 - 0
285748175.914556 - 200
285748175.924556 - 300
285748175.934556 - 0
View 7 Replies
View Related
Jan 4, 2005
Hello all - One of my 3 users' computers keeps coming up with an error when I try to enter the following command in the "Validation Rule" field of the table design view:
>=#1/1/1998# And <=Date()
Some investigative work showed that it's erroring on this clause: "<=Date()". Any idea why it would error on her pc and not the other 2 pc's? Her pc is old, but we're all using Access 2002. The error says: "The function you entered can't be used in this expression." Any thoughts would be helpful. Thanks much.
Dan
View 3 Replies
View Related
Nov 22, 2006
I am trying to find a way to have a date and time stamp applied to records that I am importing from excel. I have not had much luck, and woudl appreciate any help.
View 4 Replies
View Related
Jun 9, 2015
I have a series of forms in my shiny new database and I have added the auto time and auto date fields to the form headers. I have added the code to keep the time ticking over and it all works fine.
Unless the is no data. If for any reason there is nothing in the Details part of the Form, the date and time do not display. As soon as any data shows up, so do the date and time.
The VBA code is running in either case so I am a little baffled. Usually Google is my friend but not in this case.
I am using Access 2013 on a win 7 machine.
View 5 Replies
View Related
Aug 20, 2013
I have several forms will be filled out on a daily basis by 50 different people. Does Access have a way of allowing multiple users to fill out the forms at the same time and still track the data? Or is it like Excel and can only be viewed as "Read-Only" once opened by someone else?
View 5 Replies
View Related
Sep 23, 2013
I am trying to write an expression that will result in a date in the format (mm/dd/yyy). I want to display the curent day if it is before 11:00 am based on the system time, and the next day if it is after 11:00 am. Below is the expression that I currently am working with... but it is not working.
IIf(Time()>Hour(11),Date(),DateAdd("d",1,Date()))
View 5 Replies
View Related
