Date Selection Help
Jan 9, 2007
I currently have a query that takes info from an asset support table.
I have a Support Contract Start Date in the table (Date time)
I have a Support Contract Length In months field in the tabel (numeric)
i am calculating the end date of the query using the following expression.
Support End Date: IIf([Support Start Date] Is Null Or [Support Contract Length] Is Null,"N/A",DateAdd("m",[Support Contract Length],[Support Start Date])
this Works fine and I get a Date however many months in the future that the contract length in months field dictates.
however i am now trying to add criteria to this in order to select certain date spans of when a contract ends.
So i only get records in a query that show records with end dates in 30 days time from now or another query that shows contracts ending in 365 days from now.
Can someone point me in the direction of how to do this effectivly , as i need to generate a report from this query that will show me support contracts expiring in 1 year and in 30 days time.
Thankyou for your time and help
View Replies
ADVERTISEMENT
Mar 15, 2007
Hi
I have this expression in a query on which a report is based and would like to use Between And to select the date period, but cant get it to work, the table has Feb and March dates in it but the query brings out March dates if I select 01/02/07 and 28/02/07 (UK date format) ..it works if I put for example "01/02/07" in the criteria of the query ie it shows all records dated 01/02/07
MyDate: Format([LogInTime],"dd/mm/yy")
As always any help, advice, pointers would be gratefully appreciated
cheers
Fi
View 1 Replies
View Related
Jan 14, 2008
Hi, I have a database which contains deliveries made for the last 24 months i need to select only those made within a selected time period i.e. last month, Does anyone have any idea how this can be done?
View 1 Replies
View Related
Nov 19, 2004
I have a table to store the new append records from a SQL statement. In this table I have defined a "Status Date" in field type date/time with format dd/mm/yyyy. However, when execute the SQL append statement, the system has appended the record with format of dd/mm/yyyy hh:mm:ss.
The problem is when I run the report with a date range input, the report cannot select any records with such date range. I have examined the table format is correct, but I found the data actually included the time in it. Do anyone know how to overcome this problem?
View 3 Replies
View Related
May 23, 2013
I need to select a record with the latest (max) date along with associated values that go along with that record. However, some records have the same date. Initial query sorts by Asset_ID and Date (Desc)...A second query against the initial query then groups by Asset_ID, taking the Max Date and First Switch and Port...Results show the latest date but with a different Switch and Port
Asset_ID | Date | Switch | Port
123 23/05/2013 WAR01 GI01
123 23/05/2013 SAM01 GI02
123 20/05/2013 ROC01 GI03
123 21/05/2013 CHR01 GI04
e.g I get 23/05/2013 with ROC01 | GI03...I have tried doing this as a nested query but get similar results.
View 2 Replies
View Related
Sep 11, 2014
Below is the SQL for a query I have, which returns events that are scheduled for today, I need to change this so that The user can specify events to be displayed in a date range and If possible only show those events for the users windows ID
Code:
SELECT tblEvent.EventStart, tblEvent.EventOwner, qryCompany.Company, ltDescriptionType.Description
FROM (tblEvent INNER JOIN qryCompany ON tblEvent.Company = qryCompany.ContactID) INNER JOIN ltDescriptionType ON tblEvent.EventDescrip = ltDescriptionType.[DescriptionType ID]
WHERE (((tblEvent.EventStart)=Date()))
ORDER BY tblEvent.EventStart, tblEvent.EventOwner;
View 2 Replies
View Related
Apr 16, 2015
I have a big list of data, with a row of data for every day for each client.
I need a selection criteria that will provide show the last day of the every month (historic data) for each client.
I've had a go but I'm not making much progress.
View 4 Replies
View Related
Jul 31, 2013
I'm trying to create an query that has two separate expression in it and a date selection criteria. The first expression is,
Expr1: DateDiff("s", [Arrival Time 1],[Departure Time 1])/3600
and the second is,
Expr2: IIf([Expr1]>[Hours Per Day],[Expr1]-[Hours Per Day]
When I run this query it asks me for the date (which is fine) but then it also pops up a box asking for "Expr1". How can I get "Expr2" to use the value returned from "Expr1"?
View 3 Replies
View Related
Sep 18, 2014
I have a report based on a query that returns all info from the query which is fine, I now need to amend this so that individual users can specify the date range to be queried and the person for whom the results are required (one of the query strings)...
View 1 Replies
View Related
Jan 5, 2014
I am using this code to filter a List Box based on a Date range and a Combo Box selection:
Code:
Private Sub Combo139_AfterUpdate()
Dim StrgSQL As String
StrgSQL = "SELECT [User Name], [Date Of Request], [Description of Problem], Status, Sub_Job FROM QRY_SearchAll " & _
"WHERE [Date of request] BETWEEN #" & CDate(Me.txtStartDate) & _
"# AND #" & CDate(Me.txtEndDate) & "#;"
StrgSQL = StrgSQL & " WHERE Sub_Job = Combo139"
Me.SearchResults.RowSource = StrgSQL
Me.SearchResults.Requery
End Sub
However, It is not working. when I click the Combo box the List Box comes up blank.
View 13 Replies
View Related
Feb 15, 2015
I have been building a database for use in a charity shop and am struggling with an issue regarding one of the forms.
I currently have a form which displays all expenses from the shop. I have added a combo box to the top of this form which allows users to filter records based on an expense ID Code. It all works fine but I would like to be able to add a start date and end date box to the form also so that records can be shown between two dates.
I have tried adding parameter boxes to the query which runs the form (which works) but the issue I am having is that when a new id is selected from the combo box the parameter boxes pop up again asking me for start date and end date again. This happens every time a new combo box id is selected.
I think the way resolve this issue may be to add a start date and end date box to the form but I don't know how to implement this.
Please see the attached files for images of what I currently have. The forum won't let me post images directly here until I have 10 posts so I have had to attach the files instead.:
Query running the form - Attachment 1
The Form itself - Attachment 2
Combo Box - Attachment 3
Bound Column on combo box - Attachment 4
Code in Combo Boxes after update event - Attachment 5
View 3 Replies
View Related
Aug 17, 2014
In my main form, there is this date entry selection and a subform with data on it.
How can I make the subform filter its data from date entry selection?
I have already created a relationships for my tables
I created a query for this subform (should I need to?)
It does not refresh my subform or I do not know what to do?
View 12 Replies
View Related
Mar 11, 2015
I'm creating a form that when the user selects the following categories in the same combo box (Date Received, Date Reviewed, Date kitted, In Work, Complete) it auto populates dates in the respective fields. As I mentioned, it's only one combo box. The dates will be spread out, so the user will change the combo box selection based on when these events occur. I already have a field for each category both on the table and form. Also, I do have multiple tables for other parts of data, but these categories all fall into the same table.
View 8 Replies
View Related
Oct 27, 2014
I am designing a nomination form (web database so no vba macros can be used).
The form has 3 combo boxes: cboStaffName, cboLevel, cboNominee.
The form is bound to the tblSubmit table where the submissions are populated.
I used a select statement:SELECT Staff_List.Staff_Name FROM Staff_List; to populate the combo box for the Staff Name selection.
This is the select statement to populate the job level combobox:
SELECT Staff_List.Level, Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Staff_Name)=[forms]![frmtest]![cboStaffName]));
The select statement to populate the combobox for the nominee combobox:
SELECT Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Level)=[forms]![frmtest]![cboLevel]));
The problem is that the staff_name in the first combobox is still found in the nominee combobox which should not be because a staff cannot nominate self. There is a field in the Staff_List called YesNo that should be activated for each staff that is selected so that the select statement on the nominee combobox can be updated accordingly to remove items with the field "Active"
How to get the checkbox selected for each corresponding staff.
Sample of the database has been attached.
View 4 Replies
View Related
Jan 12, 2005
Porbably a stupid queston for most of you, but I am new to the game. Trying to get the selection of one combo box to filter the selections of a second combo box. Have one table called Sections with the colums SectionID and SectionName, a Second Table called SubSection with Colums SubSectionID, SubSectionName, and SectionID. Third Table called Points which has a column for Section, and SubSection, need to have the section combobox selection filter the SubSection combobox selections. Any help would be appreciated!
View 1 Replies
View Related
Aug 19, 2015
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
View 13 Replies
View Related
Oct 24, 2013
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
View 4 Replies
View Related
Oct 9, 2005
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.
I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.
View 9 Replies
View Related
Oct 5, 2005
I have part of a query that checks a string and returns a 2 letter code. the string is 9 characters long and consists of 1's and 0's, but it is picking the wrong 2 letter code
String Code
100000000AC
010000000AR
001000000CD
000100000MC
000010000MR
000001000PLS
000000100PS
000000010SBP
000000001TQ
The expression is as follows:
VALDISP2: IIf([DIS2]>="100000000
","AC",IIf([DIS2]>="010000000
","AR",IIf([DIS2]>="001000000
","CD",IIf([DIS2]>="000100000
","MC",IIf([DIS2]>="000010000
","MR",IIf([DIS2]>="000001000
","PLS",IIf([DIS2]>="000000100
","PS",IIf([DIS2]>="000000010
","SBP",IIf([DIS2]>="000000001
","TQ","")))))))))
However the string 000000001 is picked up as "" and not "TQ"
View 1 Replies
View Related
Dec 18, 2006
Hi I have a continuous form that has bound checkboxes next to each record. Right now the code below finds the records that are checked and edits them accordingly. I need to find a way to make only one checkbox be able to be checked at a time. Or have the code below find if 2 or more checkboxes are checked, then prompt and exit sub.
Private Sub Command43_Click()
Set rs = Me.Recordset
Dim user As DAO.Database
Set user = DAO.OpenDatabase("c:auser.mdb")
Set rs3 = user.OpenRecordset("currentuser", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
rs.Edit
If rs!dispositionchk <> 0 Then
rs!status = "Do Not Call" 'change status to to be contacted
rs!dnc = "DNC"
rs!lastlo = rs!Assignedto
rs!Assignedto = " " 'assignedto=current employee name
rs!LDdate = Now()
rs!LDdisposition = "Do Not Call"
rs.Update
Set rsmyrs = CurrentDb.OpenRecordset("internetcalls", dbOpenDynaset) ' open table
rsmyrs.Edit
rsmyrs.addnew
rsmyrs!ActivityDate = Now()
rsmyrs!disposition = "Do Not Call"
rsmyrs!LeadID = rs!LeadID
rsmyrs!Employee = rs3!Username
rsmyrs.Update
Set rsmyrs = CurrentDb.OpenRecordset("call log", dbOpenDynaset) ' open table
rsmyrs.Edit
rsmyrs.addnew
rsmyrs![Activity Date] = Now()
rsmyrs!disposition = "Do Not Call"
rsmyrs![Lead Id] = rs!LeadID
rsmyrs!Employee = rs3!Username
rsmyrs.Update
rsmyrs.Close
End If
rs.MoveNext
Loop
Me.Requery
End Sub
View 1 Replies
View Related
Aug 5, 2005
I am creating an online post, similar to a guestbook. When a user submits an entry, I am storing the date and time in a column titled "Timestamp", which is formatted 8/4/2005 9:16:58 ("General Date"). I am running the table through a query which is then posted on the web.
What I would like to do is this: I would like to use Timestamp to display the date that the entry was submitted, but not the time (basically, "Short Date"). Can I exclude the time using a query without changing the actual data stored in each record of the table? I understand that the time stored is completely different than the time displayed, even in the table. How can I do this?
View 2 Replies
View Related
Apr 21, 2006
hii do a little check before run codePublic Function validationValue() As Boolean If lstTransit.ItemsSelected.Count > 0 And txtDate.Text <> "" And txtHeureDebut.Text <> "" And txtHeureFin.Text <> "" Then validationValue = True Else validationValue = False End IfEnd Functionafter i check the resultDim fctReturnn As BooleanfctReturnn = validationValueIf fctReturnn = True Then..else msgbox "erreur..."endifwhen this code run, i get:Run-time error '2185'You can't reference a property or method for a control unless the control has the focuswhat is the correct way to validate user input?any idea?thanks
View 1 Replies
View Related
Sep 20, 2005
Can anyone help with this. I have a query called trigger, which has 5 fields as follows
CONTACTID..............INV...............MSP...... ........SMS..............TRIGGER
456.............................................11 ......................................MSP
457..........................8.................... .......................................INV
458.............................................3. .......................................MSP
459............................................... ...................6...................SMS
Only 1 field between INV, MSP and SMS in any record will be >0
What I need to do is that if the value in these 3 fields are > then populate TRIGGER with the correct name
Can anyone help
View 1 Replies
View Related
Jan 30, 2006
I want to make sure that only one check box can be checked and also to make sure that on is selected, i.e. to display a warning that one is not check if someone is going to save the record. Any ideas guys?
Thanks
View 1 Replies
View Related
Oct 12, 2006
Now, I don't know if this topic should be in forms, queries or reports, so I decided to post it here in general until someone gives me a big bollocking and send this topic elswhere :D
I have a table with loads (50+) of columns which hold information about people in our company.
What I want is a form? that allows me to tick the information I want and then displays the result in a report.
So, today I want a list with Name, address, town and phonenumber, but tomorrow I might need a list with Name, phonenumber, passport number and expire date.
In short, I need to make a 'dynamic'? report that displays the options I ticked out of the 50+ options (does this make sense?).
I searched for it but no luck so far.
Ta in advance
View 4 Replies
View Related
Dec 29, 2006
Hi
I have a db of about 10,000 records or so. This is a list that will be used for emailing purposes...and I want to limit the number of records per company that are emailed to.
I need to figure out a way to limit the number of records per company....any suggestions?
Thanks
View 1 Replies
View Related
