Hi. I need create a query that can compare different time values. My time field format something looks like 12:59 PM. And compare other fields that has +- 10 minutes from my input. It means when I input 12:00 PM to the query inputt then it lists records that have times 11:50 AM and 12:10 PM. But I don't know how to compare these..they are not natural numbers..but they aren't text niether.. I tried to do it [Arrive_Time] + 10 [Arrive_Time] - 10 in the criteria..but..doesn't work.
what i nid is the query showing date and time where time is between 8-530 and date is between (user-defned). it counts how many instances in the datebase occurs during the set date between the said hours.
ex.
date time(8-530) 1/1/2004 30
field name used was [start time] where start time is a date/time data type
ex.
start time 9/20/2004 6:43:47PM
the ff is what i'm using and it doesnt work:
SELECT Calls.[Start Time], COUNT (Calls.[Start Time]) as Exp1 FROM Calls WHERE Calls.[Start Time] Between [Date1] And [Date2] AND Calls.[Start Time] Between 8:00 AND 17:30 GROUP BY [Start Time];
In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion.
test_id | test_Name | test_Date | test_value ------------------------------------ 1 | Company A | 01.01.2010 | 0,90 2 | Company A | 02.01.2010 | 0,95 3 | Company A | 03.01.2010 | 1,10 4 | Company A | 05.01.2010 | 1,05 5 | Company B | 01.01.2010 | 0,9 6 | Company B | 02.01.2010 | 1,20 7 | Company B | 03.01.2010 | 1,15 8 | Company B | 05.01.2010 | 0,95 9 | Company B | 07.01.2010 | 0,80
The important level is 1,00
Now I will, for each company, check:
- Is the value of ID-1 >1 AND the value of the ID-2 <1, THAN give me a 1 - Is the value of ID-1 <1 AND the value of the ID-2 >1, THAN give me a -1
In this example the result should look like this:
Code: test_id | test_Name | test_Date | test_value | test_Code_result ------------------------------------------------------------- 1 | Company A | 01.01.2010 | 0,90 | 2 | Company A | 02.01.2010 | 0,95 | 3 | Company A | 03.01.2010 | 1,10 | 4 | Company A | 05.01.2010 | 1,05 | 1 5 | Company B | 01.01.2010 | 0,90 | 6 | Company B | 02.01.2010 | 1,20 | 7 | Company B | 03.01.2010 | 1,15 | 1 8 | Company B | 05.01.2010 | 0,95 | 9 | Company B | 07.01.2010 | 0,80 | -1
Probably I have to carry out this in vba.but i dont know how I can calculate across the datapoints (rows).
For each company, I would like to compare the rating from 201201 and 201202 (and keep going for each month) and if there's a change in the rating, update rating_change to the difference in rating values.
I'm trying to compare a Currency formatted field to a Number formatted one and getting wrong results.other then formatting the Number as Currency too ? Tried to use Val() but the Currency returned 0
I am making a dp for end of day sales, and i want to compare data in a table to the numbers inputted in a form and see whether this value is higher or lower. the table displays the target sales figures for each day, and the form allows the area mangers to enter in the current day figures.
I'm working on creating an events log. In my table I've got a field that pulls the individual's username (that they use to log into the computer) and logs it against their log entry.
As part of my query (for my daily events log report) I'm pulling up said username.
I'd like to give access to the specific user to edit his (and only his) entries from the report, but I'm getting a type mismatch with the below code:
Code: If me.UsernameID = "KraussLJ" Or Environ$("Username") Then DoCmd.OpenForm "frmShiftLog" DoCmd.FindRecord Me.ID, acStart, , acSearchAll, , acAll Else MsgBox ("You are not authorized to edit this entry") End If
(before you ask, "KraussLJ" is our ops manager who insists on having final editorial control over the shift log before sending it higher up the chain)
I can get the msgbox to run if I block out the
Code : Environ$("Username")
, but beyond that I'm a little stuck. Should this be a dlookup function or am I overanalyzing this way too much?
I am trying to concatinate string in a loop to generate a dynamic SQL to compare 2 tables. But I am not getting the result I want
Code: Set rs = CurrentDb.OpenRecordset("r1") Set rs2 = CurrentDb.OpenRecordset("r1_old") columnCount = CurrentDb.TableDefs("r1").Fields.count strSQL = "" For I = 0 To columnCount
[Code] .....
I am trying to achieve something that reads like the following
r1.Field(0) = r2.Field(0) AND r1.Field(1) = r2.Field(1) AND r1.Field(2) = r2.Field(2) AND .....
I have a form where when the user clicks on the browse button then excel workbook filepath gets stored in the textbox as below:
Code: Private Sub CommandButton1_Click() ChooseFile End Sub Sub ChooseFile() Dim fd As FileDialog Set fd = Application.FileDialog(msoFileDialogFilePicker)
[Code] .....
Please see attached the excel workbook. Everytime the user will select Excel workbook using Browse button. Now in that file , the first sheetname will always be "Summary". I want to perform the following steps:
1. So now I want VBA code to copy the data from columns "Withdrawn","Obsolete","Updated","LitRef" from Summary sheet to the Access table named tblSummary.
2. When the data gets copied in Access table then write So VBA code that will check if the data in field LitRef in table "tblSummary" is present in field "Reference" of Access table "tblliterature" . if its present then check in the tblSummary , which corresponding fields out of "Withdrawn","Obsolete" and "Updated" stores "Y" . 3. If "Withdrawn" field value is "Y" then change the status of corresponding record of tblliteraure to "Withdrawn" 4. If "Obsolete" field value is "Y" then change the status of corresponding record of tblliteraure to "Obsolete" 5. If "Updated" field value is "Y" then change the status of corresponding record of tblliteraure to "Updated" .
I am building a tracking database where we would be able to track information which field are null data and report them to our Administration to fill the null data. For this I have created a form name "Search" and I have a combo box control on that form which is bound to "Table = employee" and its ROW SOURCE TYPE="Field List", I would like to able to query records where the selected value in this combo box is null through out the table.
For example if I select "Telephone" from this combo box dropdown, I would like the query to show all the records where the "Telephone" is null, how to set the criteria in query to take the combo box value as "Field Name" and then compare it with the Field/Column in the table and show the null values.
I have a working dB which can calculate a shift duration and sum total all shifts worked within a period for the purpose of producing a labor report for payroll. I have successfully used the DateDiff function and converted the minutes to HH:MM on my form and reports. Now I want to calculate elapsed time for a specific period within a shift, I'll call it OtherHours and I am aiming to calculate a portion of time that meet the following conditions below. I am using field names of [PunchIn] and [PunchOut] and both are of type General Date.
IF [PunchOut] ISNOT Saturday,Sunday EXIT FUNCTION ELSE IF [PunchOut] ISNOT Between Midnight and 0559 hours EXIT FUNCTION ELSE DATEDIFF ("n", <MIDNIGHT>, [PunchOut])
My thoughts are to solve the DateDiff portion and then figure out how to apply the conditions within the IF statements.
I have to write a code for my database,i have folder with files "pending Review" and a table with column "tblExcelLocation". when i run my database all the files from pending review folder goes to "tblExcelLocation" on a click of button.But,if the files already exists it should not insert those files and insert the rest.For this i tried to write a code but i think i m unable to do that .
Code: Loop through files in folder folderspec = "O:QA FilesQC ReportingPending Review" Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(folderspec) Set fc = f.files
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 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?
Previously my snapshot file was displaying thur this code but as 2010 Access, it produded subjected error and halt at below red color line.
Public Function LoadSnapshotFile(snpCtl As Object, strFilePath As String) As Boolean Const conSnpFinishedDownload As Integer = 4 With snpCtl .SnapshotPath = strFilePath
[Code]....
And it was runing well. But not now due to the produced error. I tried to make it pdf extension of the report file. It is updating the file at the path (where I kept the report) but problem is not displaying after click event on the form.
I have a table with several fields including the following 3 fields:
dtmAppoint Date = stores appointment date dtmAppointTime = stores appointment time lngAppointDoctorID = stores doctor's id for specific appointment
When I enter a new appointment, I want to know whether a specific combination of these 3 fields already exists. The doctor is actually a dentist and maybe another appointment has already been placed for the same dentist on a different chair. Creating a recordset as indicated below works but I am having some problems with comparing Time (dtmAppointTime). The following works but I need to extract Hour, Minute and Second and put it into string variable strTimeDum.
I have created a report in Access for around 800 uniquely identified line items. My end goal is to develop a script that will Print a PDF for each unique identifier and save them all in one folder and have each one named by unique identifier. Below is the script I have written, I cannot get through the Run-Time Error 91. The data is being derived from a SQL database.
Private Sub Command35_Click() Dim rst As ADODB.Recordset Set rst = Currentdb.OpenRecordset("SELECT DISTINCT [Unique_Identifier] FROM [tbl_questionnaire] ORDER BY [Unique_identifier];", dbOpenSnapshot)