I am trying to run a query to print invoices that are 30, 60, and 90 days past due. What expression do I use to search for records that were purchased 30, 60, 90 days prior? I was able to find the expression to add time for the due date but was unable to find the expression to subtract time for my search. Thanks for any help!! Beck
View RepliesI have a form in which the user inputs crane inspection results (a series of check boxes). When the user clicks the save button, I would like to run a query (I guess a query is how to do this) that searches records for the past seven days (less weekends and holidays) and alerts the user to days that inspection results were not entered for that particular crane (cboCrane.Value) and that particular shift (cboShift.Value) via a message box.
How do I get it to only look at records for the past 7 days (less weekends and holidays)?
Any help will be greatly appreciated.
I have a query that returns a set of records which details stock items that are older than a date given in a form. However i need to limit the results to the number of items held in stock
i.e say i want to look at stock over 1 year old, i get a list of all the stockids, and the date added.
Say there is a stock level of 3 for a particular stockid the results should be limited to the first 3 records that are over 1 year old. Rather than the whole list of dates I get now.
Any help or direction with searching terms would be appreciated
thanks:confused:
I trying to create a query that just returns all of the records in my database for the past year. I've tried to somehow do it using the date() function but haven't had much luck yet.
Any help is most appreciated.
Greetings All,
I'm not sure if this is possible but I've been asked to devise a way of viewing past record entries from an old form on a new form. Our database contains yearly records of student details; each year has a separate form. What my manager would like is that when a student ID number is typed into this year’s form, that old data from previous years is retrieved and placed in the database OR some sort of command button that searches past records for that particular student ID.
Hope that makes sense, I'm completely confused about the whole thing, I'm not sure if it’s even possible...any kind of help will be gratefully received!
I have a form for creating new entries in a table. In it there is a textbox for a City field that I would like to have some kind of auto-complete based on past records in the database--similar to how Excel provides auto-complete options within a spreadsheet based on entries in previous cells.
In other words if "Binghamton" has been entered in a past record and the user starts typing "Bing" in the textbox, then "Binghamton" will become an autofill option. Is there a way to set this up?
Hi I have a Text box that stores a date of when a problem or query is received on to a customer account, when resolved the date it is resolved is inputted in the next text box as a resolved date. My third Text box then needs to display Yes or No depending if it took less than 28 days to resolve.
Ive used a Text box and DateDiff expression to work out the ammount of days in the control source, Called query dauration then a macro to test if its less than 28 days to display the yes or no. But this is not working days Mon - Fri. Has any one any ideas?
=DateDiff("d",[Date Query Raised],[Date Query Resolved])
Macro
IIf([Query Duration]<="28","Yes","No")
I have a query that uses two criteria from a user input form. On the form, the user would input a customers last name and use a dropdown list (based on a table 30,60,90,120,365 days) to select how many days previous they want to search. Unfortunately there is something that I'm over looking and can't figure out.
Let's say I have multiple records that match the following criteria: Last name "James", days since contracted all less than 30 days. For some reason when I put in "James" as the last name in my form and select "30" from the droplist, it only lists the most current one which is zero days. Even if I select "60" from the droplist, it still only shows the zero day contract.
If I go back and select "90" from the droplist is brings up 3 - 3 day old contracts and my 1 zero day old contract. If I select 120, it picks up another contract that is 4 days old. If I select 365 it picks up a couple more, but still not everything. Here is my SQL code.
Code:
SELECT tblAllData.[F&I Manager], tblAllData.[Reviewing BOA], tblAllData.Date,
tblAllData.[Stock#], tblAllData.[Deal Date], tblAllData.[Cust First Name], tblAllData.[Cust Last Name],
Round(DateDiff("d",[deal date],Now()))/1 AS Days, tblAllData.[Bank Name],
[Code] ....
How do I write a Access 2010 Web database expression to give me the number of days between a particular field eg [sold date] and todays date?
Being a Web database I know I am restricted to a smaller list of available expressions - normally part of my expression would include eg date().
I am trying to build an expression in a query that will filter specific numbers when the value is entered. I would like the value box to read something like "Please enter the file number" and when entered, see example of file # in brackets (001-200-001), it will show those records with that file number only. In addition, because I will be printing it from a report, the filtered file numbers should also show up in the form.
Thanks.
I have a query that I need to always pull data from the newest past 3 months, so if this query were ran today it would take from months 2,3,4 (Feb, Mar, Apr). Any clue on how to do this?
View 14 Replies View RelatedHi.
i.e..............
Table A has records 1,2,3,4,5,6,7,8,9,10
Table B has records 1,2,3,4,5,6
How do I create a query that returns values 7,8,9,10 for Table B when I compare Table A and Table B?
What sort of query do I need? I tried the "Unmatched query" but this did not get the result I wanted.
Thanks for reading.....
Frank.
I have a table 45 fields. There is a field ID. I would like to get the records where fields contains "No". I would not like to create 45 queries for each field check. how can i generate a table which shows ID, Field Name that contains value "No".
View 4 Replies View RelatedI'm having a very strange problem with access. In vba I have an sql statement "select StudentID, CourseID from tblAttendance where AttendanceDate = #Whatever Date#".
Now this works for all dates (as far as I can see) before 01/07/2013 but after that it just doesn't find any records. I have manually added dates and checked and it didn't work.
How this could be possible? The date field is not in any relationship and it is Date/Time format. I don't even know how to go about troubleshooting this as it makes no sense to me.
It thinks these records have a date below 1/7/2013 and it can find dates after 1/12/2013.
Hi, i have a query which looks at a table named "attendance" the query includes the fields: "employee number", "absent code" (a 3 letter code), "absent reason" (list of different reasons) "shiftdate" (weekly shift dates in format: DD/MM/YYYY). If possible i would like to be able to promt the user to enter a employee number, then a absent reason (ideally from a list box) and show all occurences (with all the shiftdates) only of that particular absent reason within the last 12 months, and ideally calculates in another field the number of times that employee has been absent for that particular reason for the last 12 months.
Does this make sence?, i know how to promt the user to enter a employee number but not sure how to go about the rest, any help with what seems to be quite a challenging task to me would be excellent.
Now that the year has moved into 2014, the query below no longer works. It appears that it is not extending back into 2013.
Code:
Private Function multiDateClause(dateStartQry As String, dateEndQry As String, firstOp As String, secondOp As String)
multiDateClause = dateClause(dateStartQry, firstOp) & " AND " & dateClause(dateEndQry, secondOp)
End Function
Private Sub b_last7_Click()
Me.resultsFrame.SourceObject = "FollowUp_bystaff"
Me.resultsFrame.Form.RecordSource = todoListQry(multiDateClause("Date()-6", "Date()", ">=", "<="))
End Sub
I created the following expression on a report, under the details, in a text box:
=nz(pathjbo50,0)/nz(pathjbo50,0)+nz(pathcol50,0)+nz(pathpp50,0)+nz( pathlm50,0)
what I want is to find out what percentage pathjbo50 is of the sum of the other 4 fields.
but I keep getting an "Error" message. How do I get this percentage?
Where is the best place to find literature on writting expressions?
I have a database functioning pretty well.
All I did was move the files from one computer to another via USB stick and now I get this alarm when I try to run this particular macro.
It is a macro to set a value to specific field.
The Macro uses a condition [Forms]![tblCustomerCall]![Alarm Number]="000".
If the condition is met then it is supposed to set a value to a field in the same form.
[Forms]![tblCustomerCall]![Alarm Descrption] is the item to set.
Left("TV Parity Alarm",50) is the expression.
So if 000 is entered in then the text TV Parity Alarm is filled into the Alarm description field.
It was working perfect until I moved the files to another computer.
I have Office SP2 installed and have updated office with everything available.
Please help.
Thanks
Well, I'm trying to enforce referential integrity on one of my relationships. But it seems that there are 5 records in the Subtable that don't have a corresponding record in Table, so it won't work. I tried copy/pasting from Subtable to Table, and it says 'unable to make changes because this would make duplicates in the primary key' to all values except 5. Which it asks me if I want them pasted. If I click 'yes' or 'no' nothing happens either way.
Well, anyway, I need code for a query that selects all records from Subtable.Field1 that don't exist in Table.Field1.
Hello all,
I want to create a query that looks for a manual line break in a memo field. Is there a code or some way that I can do this? (i.e in a Memo field the user has entered some data, hit Ctrl-enter to get to a new line, entered more data on the new line. I want to find the records where this has occured)
Also, on a similar note, is there a way I can deny my users the option of doing a manual break (Ctrl-Enter) in a Control in a Form? (to prevent me needing to run this query in the future)
Please advise.
tia,
Angel
I'm relavtively new to databases but after a steep learning curve (being left with many databases created by my old manager with no instructions on use) I'm getting there.What I am trying to do is pull out the date from records in a field called "Model_ID". The Model_ID field contains this kind of information:
Endscopy20120726JSmith
GISurgery20120521JDoe
I want to bring back "20120726" or "20120521". In excel I can do this with the mid and find functions but find doesn't work in access. I've tried combining the mid function with instr function but it comes back as too complex!
I've got a Table with a string of data delimited by an "*". The first part of the string before the "*" can be variable in length, so if I use a LEFT expression to break apart the 2 parts of the string on each side of the "*", my results are incorrect. In MS Excel, I can use the following formula to solve the problem:
=LEFT(B6,(FIND("*",B6)))
You are just substituting the number of positions with a Find command that looks for the "*", and returns anything left of the "*". Unfortunately, the brilliant programmers at Microsoft, once again, don't have an Excel function in Access. how to recreate that functionality in a table in Access?
I have an table that contains StudentID, Name and Age. I have imported the data from Excel sheet and there are some records which contains Null Value and some "h", "n/a" etc. I would like to design query which finds the records that are non numeric.
View 2 Replies View RelatedI have a table listing xxx reasons for dispute - Users will check boxes. How do I build my query that would list all the entry numbers which have a text box?
Might sound stupid but what would be the criteria? Think it would be easier to get these results into a report and sum.
I'm baffled!19142:confused:
This may not even be possible, but I am looking to create a Query that can locate records in an Access Table based on 2 columns of data that I have stored in a CSV file.
My table contains several fields, 2 of which are "Dept" and "SKU" and has over a million records.
My CSV file contains 3 fields: "Dept", "SKU" and "Total" - total being the number of times that particular Dept/SKU combination is used.
I need to be able to parse the dept/sku values from each row in the CSV to the query and locate only the records that contain the same values in the Access table.
The plan being to delete out those that are identified by the query.
This is fairly simple i think although i cant do it right i have a field in a query named; "shiftdates" which uses the format: DD/MM/YYYY, how can i set the criteria for this to only show records from the past 7 days?
i think i need something like: date()-7 (this doesnt work though), anyone help me out?, :o