I have this select query.
SELECT DISTINCTROW L160.Date, Avg(L160.Zinc) AS [Avg Of Zinc], Min(L160.Zinc) AS [Min Of Zinc], Max(L160.Zinc) AS [Max Of Zinc], Count(L160.Zinc) AS [Count of Zinc]
FROM L160
GROUP BY L160.Date
HAVING (((L160.Date)=[Forms]![L-160quarterfrm].[Date]));
How do I build a form that would ask the user to input a range of dates for the criteria?
Thanks! :o)
Hi
This is simple im sure but i am a thicky
How do you allow a user to enter a value in a form; have access set that inputted value to a criteria in a query? Then ill have a button to run the query which i can do
thanks
thicky ste
Based on information from a earlier thread.... I created a Union query that pulls information from multiple tables and fields.
SELECT AG_B_R1 as Num FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R2 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R3 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R4 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R1 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R2 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R3 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R4 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R1 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R2 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R3 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL SELECT AG_e_R4 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll];
And then I created another query to get the STDEV of the above query
SELECT StDev([Num]) AS StDev
FROM Q_cals_ag_bme_STDEV_Union;
The result will be on a subform on my main page. How do I get my form to input the [roll] automatically and requery the subform, showing my result.
Thanks
I have a query that uses the input from a form as criteria, which is then used in a report. The form input is a drop down based on another table. This is a sales pipeline report, and the list is a list of sales people. The report works perfect for all sales people except one. When I run it for the one, I get the following error:
"This expression is is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables".
I DO NOT get the same error when running the query by itself - so assuming there is something in the report causing this. I do have some sum formulas in the report.
Again, no other salespersons selected cause this error -- so I am assuming there is something in the dataset for this person that is causing the error.
I have a report that has an underlying query that asks for Start Date and End Date.
Is there anyway that I can get what the user inputs into the box to be put into the Page header of the report?
ie: user enters into the parameters
[Start Date] 01/01/2006
[End Date] 04/04/2006
Then when the report displays it says
Report for the period 01/01/2006 to 04/04/2006
Thank you for your time
I have a database with the following information:
FTA # Date Attended Last name First name assigned person......
Each week I need to generate a report that shows the people that attended for that week, grouped by the assigned person. I only want to show the data for a specific "date attended" (i.e. that days date). I figured I first need to generate a query that only returns the data for that specific date in the "date attended" column. I did that but it is manuel by using the criteria field.
Is there an easy way to type in the date that I want the query to use as the criteria?
Thanks
:o
I have a customers list that i am always ading to .I need to run a query to see what individual customers have ordered .
I have the query that works great when i manually insert the customers Surname .but i want the combo to do this job .Is it possible ,or should i be doing something different .
I am trying to get my VBA code to dump a query once the user pushes a button. I have the following code to call up the Excel app.
Code:
Option Compare Database
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
[code]....
The qry_PP_Errors_Union is a Union query. In this query there is a date field. I would like to be able to to use that date field as a parameter. So I have written this VBA to prompt the user for a Begin Date and an End Date.
Code:
Beep
strBegindatemsg = "Enter the beginning date." & vbCrLf & vbLf
strBegindate = InputBox(Prompt:=strBegindatemsg, Title:="Begin Date")
Beep
strEnddatemsg = "Enter the beginning date." & vbCrLf & vbLf
strEnddate = InputBox(Prompt:=strEnddatemsg, Title:="End Date")
Now the part that I am missing is that I am not sure how to make the "strBegindate" and "strEnddate" the criteria for the union query.
The following is the SQL for my union query.
Code:
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, UICError AS Error, "Update and Internal Correspondence" AS Category FROM qry_PP_UIC_Error
UNION
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, BIDError, "Bids" FROM qry_PP_Bid_Error
[Code] ....
Without the criteria, my code works for dumping everything out into Excel. However, dumping all the data results in a 7 mb Excel file that requires manual deletion of the information that is not pertinent.
I have a table which list a load of items, one field is date and one field is time.I have a form with two date boxes and two time boxes, the idea is for the user to search between the two inputted dates and the two inputted times.This then runs a query for a report to be produced. The problem I having is getting the query to runs both criteria it returns nothing.here is the Where part of the current SQL.
WHERE (((tblIncident.IncDate) Between [Forms]![FRM_SearchMulti]![txtrepdate]
And [Forms]![FRM_SearchMulti]![TxtrepDateB]) AND ((tblIncident.IncTime) Between [Forms]![FRM_SearchMulti]![txtreptimea]
And [Forms]![FRM_SearchMulti]![txtreptimeb]))ORDER BY tblIncident.IncDate, tblIncident.IncTime;
I am currently creating a form to input new return parts into a database. I am trying to automatically generate a tracking number (##-AA-####-####). I have gotten myself to generate the ##-AA-#### in a list box and almost was able to generate the sequence number, 0001, 0002, etc. using the dmax function. I would like to generate the sequence number one higher than the highest, depending on part type and last 4 digits of part number. Our parts have unique last 4 different p/n but more than one can fall under the same type.
View 14 Replies View RelatedI have text field that is storing both dates, and "N/A."
I'm trying to to get only records that match this format "mm/dd/yyyy" or "xx/xx/xxxx"
Are there any format functions for query criteria, like must match this format "__/__/____" ?
I know I can just use "<> "N/A"" however I want "<> "N/A" AND Format = "__/__/____"
I am trying to create a parameter query to return dates that have 2 years remaining.
For example I have dates for when mortgages expire, and I want to recognise the dates that have two years remaining using a parameter query but I can't figure out if I use DateAdd or DateDiff.
I am new to VBA and I'm trying to write a query that will update a table with dates based on user input. For example a user will run data each Monday and that date will be day01. The date table has 28 days total and I need each day row to update with the next date i.e. day01 is 12/30, day02 is 12/31, day03 is 1/01 etc..I am having issues just running the update the query. I get a too few parameters error message on the strsql statement. There are only two columns in the table, order_day(date column) and date_value(text). I want to update order_day. I also need creating a loop so it knows to go back and add days to the other values.
Here is what I have:
Code:
Sub Update_Dates()
Dim rs As Recordset
Dim db As Database
Dim lmsg As String
Dim transactiondate As Date
Dim strsql As String
[Code] ....
On my main form, I have a subform to input multiple dates, using date picker.
I also have a text box on the main form to input single dates, again using Date Picker.
On entering both the single date text box and the subform, I don't want the (default) date to be visible. I only want the date to be visible once I have selected a date from the Date Picker.
I have figured out how to do this with the single date text box. I have simply defaulted the forecolor to be white so that you can't see the font against the white backcolor. Then on the Change Event, I set the forecolor to be black. Works like a charm.
However... This same approach will not work with the subform. I've tried playing around with a bunch of different events, but so far nothing allows me to re-create what I am able to do with the single date text box on the main form.
I have the following query.
SELECT Table.*, Table.Date
FROM Table
WHERE (((Table.Date) Between [start date] And [end date]));
Is there anyway I can format the box that prompts you for the start and end dates? I would like the format to be, mm/dd/yyyy ? Any help?
hi,
new to the forum so hello everybody,
what i have is a table of contacts. this table has a field called locations. i have created a queriy that askes me the location which works ok. i could also have the criteria that has like "lo*" in it
but what i really need it the query to run ask me the location and i only put in LO* OR BI* etc
and the results i get are either london or birmingham etc
thanks for you help
steve
Hello,
I need to create a field in an input form that is simply the concatenation of two other text fields. I have tried all sorts of things, but when I look at the data in the table that field.
I have a field called ID that I want to be created like this:
=Format([UniqueID],"00000") & "-" & [Mosque]
This works well in my output fields, but does not work the same way on the input form. It needs to be based on the currently input values from the current record. Anyone have any ideas?
Thanks in advance,
--Robert
Last question.. honest.. maybe ;)
I have a query that prompts the user to display certain/all of the members in my member table. The problem is, using a parameter query in the memberID field only allows the user to send the message to one member, not multiple members. "1 or 2 or 3" or "1,3" obviously don't work.
I've tried creating a form with a list box to select the members (which I;ve done) but obviously you need code and stuff to get it to work which I think will be too complicated. Is there a simpiler way or is the form the only thing that will work?
Thanks
Kris
I seem to have run into a snag. I'm pretty sure this would work otherwise:
DLookup(iif(Left( [GenericFieldname1], 4) = "C101","Offered","NOT OFFERED"),"Generic Query")
When I added this field/function to the query, I got #error values and when I clicked on one, I got an error that said:
The expression you entered as a query parameter produced this error:The object doesn't contain the automation object 'Please Enter Year'.'
I assume the problem is that I have the user filter the report by use of the Like() function for [Year] in the query when the report is accessed. How do I tell the DLookup function to search the column as filtered by the Like() function?
Hi,
Is it possible to customise the input form for a query. At present I have to type in a start and end date manually, it would be far easier to use a popup calendar to select dates. I also have another query that requires you to type in a username, it would be preferential to have a drop down list to select a name from.
Are either of these things possible? or will I need to create a form that calls the query and passes the relevant data?
Many thanks
nit
Hey Folks,
I have a form that has two things on it:
A button to open a query
A list box with names in it
How do I get the query so that it will select all records where the name field matches the chosen name from the list box?
I am not a programmer and I don't know VBA/VB.
I am using this as a workaround to avoid VBA functions (since I don't know them).
I can't seem to find a simple? solution to this.
I have a query that does a radial search in decimal degrees. I have created an unbound form as a dialogue box that converts degrees-minutes-seconds to decimal degrees and displays the results in a calculated text box. What I want to do is have the query take the results in the calculated text box as its parameters (along with a third parameter- distance) without prompting the user. How do I get the query to take its results from the fields on the form? I have tried Like [Forms]![frmName]![SearchValue], but I can't seem to make it work.
How can I have a user enter the WHERE criteria in the Set Rs statement below.
Set Rs = CurrentDb.OpenRecordset("SELECT [Net] FROM [tblRecap] WHERE [Yr] = 2000 and [Event] = 'lancaster'")
I would like a message box to ask the user to define the [Yr] = and [Event] = fields since they would change from time to time and then pass the values to the OpenRecordset method. Is this possible? Can someone help supply the code?
A parameter query won’t work because when you manipulate these queries in DAO through VBA you need to supply the parameter value before you open the recordset object. If you don’t DAO generates an error.
Here is my code:
Option Compare Database
Option Explicit
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim ConsqWin As Long
Dim ConsqLoss As Long
Dim tmpWin As Long
Dim tmpLoss As Long
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("SELECT [Net] FROM [tblRecap] WHERE [Yr = 2000 and [Event] = 'lancaster'")
Do While Not Rs.EOF
Do While Rs!Net > 1
tmpWin = tmpWin + 1
Rs.MoveNext
If Rs.EOF Then Exit Do
Loop
If Rs.EOF Then Exit Do
If tmpWin > ConsqWin Then ConsqWin = tmpWin
tmpWin = 0
Rs.MoveNext
Loop
Set Rs = Nothing
End Sub
By the way the YR field is a number and not a date so the WHERE clause doesn’t need #2000#
Any help would be appreciated.
I have a database that has several tables, each table has account numbers in
them. I also have queries set for each table to bring in desired
information. Is it possible to set up a form so the user can input an
account number and depending on which table the account number is in, that
query will run?
I maintain a grade book application that uses many queries whose results are determined by "school year". Most of these are reports and I have a combo box on the Print form for that allows the user to select the school year. The criteria field of the several queries derived by school year is:
[Forms]![Main Navigation]![Print Form]![SchoolYear]
SchoolYear being the combo box control.
They work fine.
I have now added a function to export data to Excel. This is done in a VBA module and I am using a query to select data for the record set I use to write to Excel:
Set objRst = Application.CurrentDb.OpenRecordset(strQueryName)
When I hard code the school year in the query criteria field (i.e. "2012-2013") the process works fine, but if I revert the query to point to the print form field as above, I get an empty recordset.
The Excel export is executed from a control on the Print Form, so the form is open and the combo has data showing, just as it is when a report is run whose data is derived from a query.
When I execute the query from the VBA module, the query is not getting the school year selected on the Print form passed to it properly.
I have a form set up and would like to have field update to a table when a policy number is input into the form. The fields are extracted from a linked table and are not viewed on the form but need to be written to a table to create a report. I'm confused on the sets to take to handle this. I think i need to use the "onchange" property and set up a macro that runs a query but how does the query write to the table?
View 1 Replies View Related