I have a query with 2 time fields, a start time, and an end time. I am subtracting the endtime from the starttime and I am getting funny numbers. Example: 12:12:04pm - 12:05:20pm and I am getting #Error. Where am I going wrong? Your help is greatly appreciated.:eek:
View Repliesi just know some query designing and some amount of VBA for making small event procedures.I want to know how we can use VBA when our queries becomes complex to replace the queries and to extend the functionality.how to use recordsets in a VBA function.
View 5 Replies View RelatedI have a form that includes two basic fields
1. USDateandTimeStarted
2. USDateandTimeFinished.
The fields include both date and time (date and time are in the same field) i.e. “6/26/2006 4:23pm”. The next field I need to calculate the duration, basically one field minus another…anything I’ve tried will only calculate the time difference will not factor in the days. For example, if it has been 52 hours (2 days, 4 hours) it will only say 4 hours. A few of what I’ve already tried are below. And I’ve also tried an elapsed time expression.
Can anyone provide any guidance.. ? Thanks!
=[USdateandtimestarted]-[USdateandtimefinished]
=DateDiff("n",USDateandtimestarted,USdateandtimefinished)
Glad tidings -
Claw
Im not sure if this is best place to put this but...
I have a table where date and time is stored in one field. It is stored in the following format:
dd/mm/yyyy dd:hh:mm:ss
The way the table works is that the date & time gets associated to a status code in the system. So for example a case (lets say ID is 12345) in the system may have a status assigned of ENQ with a date of 01/01/2008 00:00:00:00 and then another record for the same case (12345) where the status is WIP with a date of 15/02/2008 00:00:00:00.
What I need to do is do is a time taken calculation between the 2 dates and times but I am not sure of the best way to do it, if at all.
Any help would be greatly appreciated.
I need to calculate (in a query) the Actual Time of a job. I have the start date, start time, end date & end time (all separate fields). This seems to works ok by subtracting the start from the end.
Then I need to calculate the Estimated Time (time it should have taken). This is done by taking the Quantiy divided by the Rate-Per-Hour. This seems to work out ok in the Query, e.g., 101000 / 15000 = 6.66667 hours. But when I display this result in a report as a "Short Time", I get 16:00 instead of 6:40. How can I get the correct display?
The other problem is that I need to divide the Estimated Time by the Actual Time. This also gives me wierd results - I suppose because one is a decimal format time and the other is a "Short Time" format. How can I get the correct answer?
I work for a bank and have build a few little DB's for differant groups but my current project is a work flow tool for a department. I have it 90% completed and i still have to build in the reporting side. Part of the reporting is caclulating times and this is where i am having problem.
I have some coding built but i am getting inconsistant results, as an example if i am trying to calculate the total amount of time between [start Time], [End Time] then minus any [Stop Time].
Code:
Me.Total_Time = Format(Int(Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM])), "00") & ":" & Format(Int((Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM]) * 3600 - (Int(Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM])) * 3600)) / 60), "00") & ":" & Format(((Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM]) * 3600 Mod 60)), "00")
I have this on a command button, i have this same code for a few other calculations and in some cases it seems to be working i have just changed my start and stop fields along with what stop time to take out in each case. I have attached some parts of my DB, one of the modules, my main table and the form where i am running the coding.
Hi,
My boss wants me to program in visual basic, instead of using queries, a calculation between times, which values are gotten from a table. How do I perform a calculation between two fields from a table in code and what functions are there to calculate the amount of difference in minutes between times in visual basic? THANK YOU SO MUCH IN ADVANCE!
G
Is there any way of making data that is inputted in lower case to automatically change to the first letter of each word being a capital ...
View 4 Replies View RelatedI have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
hi
help is needed yet again :-)
I know when creating a text field in the format option
you can use the > or < sign so that when text
is entered it automatically changes it to uppercase or
lowercase - but i need it to be Title Case, any one know
how I can do this....
I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.
The fields of the query should be:
MaxOfEverInSchool
MaxOfSurvivalToGrade
MaxOfEverInSchool
With these being the calculated fields:
MaxOfSurvivalToGradeCum: MaxOfEverInSchool*MaxOfSurvivalToGrade/100
MaxOfCanReadCum: MaxOfEverInSchool*MaxOfSurvivalToGrade*MaxOfCanRea dCum/10000
Any insight would be greatly appreciated--I just can't understand why Access would suddenly refuse to do math.
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:
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?
I have an Option Box that contains three Report Choices, Report1, Report2 and Report3. I want to run the Reports. I coded using If-Then and it worked out fine. See code below:
Private Sub Frame0_Click()
If Me.Frame0 = 1 Then
DoCmd.OpenReport "Report1", acViewPreview
ElseIf Me.Frame0 = 2 Then
DoCmd.OpenReport "Report2", acViewPreview
Else
Me.Frame0 = 3
DoCmd.OpenReport "Report3", acViewPreview
End If
End Sub
I would like to get the same result using "Case." However, when I use the Case format, it does not show all the reports as it does in the IF -Then format. See Case format below:
Private Sub Frame1_Click()
Dim Test As Integer
Select Case Test
Case Me.Frame1 = 1
DoCmd.OpenReport "Report1", acViewPreview
Case Me.Frame1 = 2
DoCmd.OpenReport "Report2", acViewPreview
Case Me.Frame1 = 3
DoCmd.OpenReport "Report3", acViewPreview
End Select
End Sub
Something simple is missing at the top. Please advise as to how to correct this?
Someone was helping me out and sent me the attached code to connect to a SQL server from Access2000 in a query. Access doesn't use the CASE statement, but the IIF statement instead. Can any of you help in deciphering the below CASE statement into an IIF statement?....
View 2 Replies View RelatedCan I use a [system number] value stored in a table in a select case?
Select Case [system number] "Tble system numbers"
Case 42144
do this
Case 88754
do this
???
Jon
I have this code in an event property on a report and it is working great. However, I now need to do a second case based on which modules are down. Is it possible to have a select case within a select case? I have the select case for the systems now with in each system I need a select case based on modules?
Dim dbs As DAO.Database
Dim rst AS DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * From YourTable")
While Not rst.EOF And Not rst.BOF
Select Case rst![System Number]
Case 42144
' do this
Case 88754
' do this
Case Else
' do this
End Select
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
Set dbs = Nothing
How can I set up a table so that as we assign a case it generates a new number with the year in front of it. I would like for the case number to be generated by the program and when someone enter a new case the program generates the next case number and assignes it to that case. The format we use for our file numbers are year-XXXX (ex: 07-0001). With the new year I'm trying to set this up so that as we start the year the computer will generate the new number.
View 1 Replies View RelatedAny ideas how to Capitalise data in a table.
View 14 Replies View RelatedHi,
I am trying to run the following query and getting an error. It looks like its because of the Case Statement.
SELECT Role.RoleID, Objects.ObjectID,
CASE MID(Objects.ObjectName, 4, 2)
CASE 'SR'
0
CASE ELSE
2
END AS AccessType
FROM Role, Objects
WHERE (Role.RoleID > 2115)
Can you give me suggestions plzzz
Thanks,
Hi everybody,
I'm having quiete a problem with the case instruction. I have the folowing query:
Select station, count(case poc_1 when 1 then 1 end), count(case poc_1 when 2 then 1 end)
From poc
group by station
Well Acces tells me there is a syntax error in the case instruction???Can't find!!!Can anybody help? Tanx
Hi,
I'd like to know if the keyword CASE, or IF... is recognized in Access (sql) queries.
If not, wat should I do to implement a condition, in a query or in the source of a textbox in a report ?
The problem : I have a report that use this query in a calculated field, and I want to avoid results that are equal to zero (i.e.divising by zero).
Thanks :)
Twinpath
Hi here is the original code
MAX(IIf([Book]='C',[Score]," ")) AS CScore,
Max(IIf([Book]='C',[PercentileScore]," ")) AS CPercent,
Max(IIf([Book]='C',[PassFail]," ")) AS C,
Max(IIf([Book]='D',[Score]," ")) AS DScore,
Max(IIf([Book]='D',[PercentileScore]," ")) AS DPercent,
Here is what I thought may work, but it seems like there should be an easier way
Case when Max ([Book]) = 'C' then [Score] else null as CScore,
Case when Max([Book]) = 'C' then [PercentileScore] else Null as CPercent,
Case when Max ([Book]) = 'C' then [Passfail] else Null as C,
Case when Max([Book]) = 'D' then [Score] else Null as Dscore,
Case when Max ([book]) = 'D' then [PercentileScore] eslse Null as DPercent,
Thanks
K
I have two tables that have fields set to a text so that the ClientID is their name.
When I query, my queries don't take into account the case. So "K Smith" is the same as "K SMITH" as "k sMIth"
I am trying to write an unmatched query between two tables based on this ClientID but it will turn up no unmatched because it is not taking into account the case.
Any suggestions on how to match the cases, othere then changing the table?
Thanks.
Hello, I'm attempting to use a case statement in order to select fields from a column that have names and addresses mixed. I'm wanting to select only the fields that contain names. For example some fields start with an address of "1998 Sky Rd" or PO BOX, or Suite at the beginning of the field. I would like to first select all fields with names in the field and then use an update statement to move/switch fields to another column. The only problem is that I can't seem to get this query to work. Please help!!!
Thank you!!!!!!!!!
SELECT address1, name3=address1 as expr1
Case [address1]
When mid([address1],1,1) Like [A-Z] then [address1]
When [address] is null then ""
When [address] = "" then ""
Else 'Null"
End,
FROM Exercise1;
:confused:
i have a form, and i would like that on one of the fields, as soon as i type something in it when i leave the field, i want it to automatically convert it to title case. can this be done please?
Cheers