Query Help Needed Using Values From Current Form
Mar 13, 2007
Hi i am having problem creating a query that will pull the price of a property into the price field on to a booking order form depending on the values of the start date and property number enter onto the same form.
i have a two tables property price and booking order.
Fields in the property price are property price no, property no, start date, end date and price.
Fields in the booking order table are booking no, property no, start date, end date, cost, no of days, total cost.
When in the booking order form i need the query to get the price from the property price table matching on the property no i've selected on the booking order form and also the start date i've entered on the booking order form needs to be between the start and end date fields in the property price table.
This is the query i have created but doesn't work
SELECT [Property Price].Price
FROM [booking order] INNER JOIN [Property Price] ON [booking order].[Property No] = [Property Price].[Property No]
WHERE ((([forms]![booking order subform]![start date]) Between [property price].[start date] And [property price].[end date]) AND (([Property Price].[Property No])=[forms]![booking order subform]![property no]));
Thanks for your help
View Replies
ADVERTISEMENT
Sep 30, 2013
I would like to create a command button on my form that copies values from 5 fields in the current record into a new record leaving all the other fields blank except for the new record ID.
The fields that contain the data that I want copied into a new form are:
1. Member_Name
2. Member_ID
3. Account
4. UBH/PBH
5. Assigned_WRCA
View 8 Replies
View Related
Mar 29, 2007
Great forum guys! Just had to join it :)
I've been doing Access for just under 2 weeks now, so far I'm doing ok, but I've just run into 2 problems. I hope someone can help me on them.
The first problem is on a query. I have a Column called "Type" in my query and the choices under Type could be RECEIPT, REJECT, BOTH
(BTW: BOTH should show all records RECEIPT and REJECT)
By using case statements as shown below, i can easily return records for RECEIPT and REJECT, however, i cannot get BOTH to work
The criteria i have set for TYPE in my query is,
[forms]![frm_Vendor]![opta] OR [forms]![frm_Vendor]![opta] Is Null
Case 1
opta.Value = "RECEIPT"
stDocName = "rep_search"
DoCmd.OpenReport stDocName, acPreview
Case 2
opta.Value = "REJECT"
stDocName = "rep_search"
DoCmd.OpenReport stDocName, acPreview
Case 3
opta.Value = " "
stDocName = "rep_search"
DoCmd.OpenReport stDocName, acPreview
Case 3 does not work. with " " . So what do i do here to show Both records?
My second problem is with the reports opened with the above Case code. The search routine (apart from Both) work fine. It finds the records i ask of it and it opens a report to show them, however, there are instances where no records are found, and the report still opens!!. In my search form, what code can i use to test my query for "null" data before
it even gets a chance to open the report?
Thanks in advance for any help.
View 5 Replies
View Related
Apr 24, 2015
The Lookup table has two fields containing values that are needed: Description and Amount ($). In the table that uses the Lookup, I'd like to have both values shown, but have a dropdown just once. In other words, when the user selects a description (the dropdown shows both the description and amount), can the amount be inserted into an Amount field as the description is done currently.
View 4 Replies
View Related
Aug 27, 2013
What I really need is for when the form opens, it looks at todays date, then matches current user and then goes to that record for today, if no current user there, then will goto new record..
i know, sounds complicated, and probably is really easy, but my heads not with it today, as about to get drunk as its my 40th, and got people ringing and texting and still trying to get this done....
I've included a copy of this database, named Timecards..
View 1 Replies
View Related
Feb 15, 2007
I have a query set up. I need to pass a set of current records ( as I select them in a drop-down menu) in a form to the query as parameters. How would I do it with or without VB? Thanks !
EDIT: Forgot to mention that all combo boxes are bound so they are not customized dialog-boxes which are unbound
View 3 Replies
View Related
Dec 21, 2007
Hi-
I have an Access DB with several different forms all relating to one master table.
Each record in the table can be resolved=yes or resolved=no.
Right now each user must click the resolved box to resolve the entry and then enter the date.
I have created a button that will run the update query below.
UPDATE [TBL Master] SET [TBL Master].[Resolved] = Yes, [TBL Master].[Date Completed] = Now()
WHERE ((([TBL Master].[Primary key])=[forms]![frm name]![primary key]))
WITH OWNERACCESS OPTION;
The query allows me to update the record on that form efficiently. However, I do not want to create one query for each form (30 plus) for this.
Is there a way to use the form that was used when the button was clicked without naming the form specifically?
View 4 Replies
View Related
Jan 29, 2007
Hi,
I'm new to using access SQL , I'll appreciate if someone can provide the where clause of the statement below:
Provide the count of assets with values older than current and previous versions.
This is needed to determine if we can upload these assets manually.
thanks
b2bmike
View 3 Replies
View Related
Oct 23, 2005
I am trying to resolve an issue of being able to use a query from many different forms where the query is dependent on the date selected in the current open form. Right now it calls the function getDate() which works fine. Unfortunatly the function is called before the form is fully loaded and the control I want to pass has no value/doesn't exist yet and I get a
"Runtime error 13 Type-Mismatch"
Here is the function code:
Function getDate() As String
If fIsLoaded("F_SupplierData") Then
getDate = Form_F_SupplierData.txtDate
Else
getDate = "01/01/1901"
End If
End Function
Function fIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
If Forms(strFormName).CurrentView <> 0 Then
fIsLoaded = True
End If
End If
End Function
Is there a way to detect the state of the text field itself (if it is open, closed, dirty)? Or if anyone has a better way to tie a query to multiple forms?
View 5 Replies
View Related
Mar 6, 2013
I want to have Access run a query, open a report, and print the report based on the ID number of the form that I have open. Is this possible?
The situation would be, I have a form that uses the the primary key as a record number. After I enter the data into the form, with the form open I want to add a button to open and print a report of the current record number.
View 3 Replies
View Related
Jul 13, 2014
I have a combo box that needs to only show approved values for any current data input but also be able to show legacy values (that were approved at one time but are no longer approved).How can I only allow the user to select from current approved values but still be able to pull a legacy record and show the legacy value.
Table structure
tblRepairReason
ID (Nnumber)
Name (Text)
ApprovedValue (Yes/No)
The recordsource of the combo box:
Code:
SELECT tblRepairReason.ID, tblRepairReason.Name, tblRepairReason.ApprovedValue
FROM tblRepairReason
WHERE (((tblRepairReason.ApprovedValue)=True));
View 14 Replies
View Related
Aug 3, 2006
I have a table of records, each of which has a date.
I want to create a form which has two boxes on it for the user to input 2 dates and then get Access to query the table of records and display only those which fall between the two dates specified.
I could do set this up using a parameter query but am not sure how to make the query pick the dates up from the form.
How can I do this?
Gary
View 14 Replies
View Related
May 14, 2007
Hi,
I need to build a query where upon form input a different table gets queried.
SELECT *
FROM ((Forms!DCL!PortDropDown))
WHERE ((Forms!DCL!DestinationList=(Reg_Dest.Destination) ));
The "FROM" is my problem. The "WHERE" is working. I am getting a "SYNTAX ERROR IN JOIN OPERATION" error message. Please help.
Thank you:confused:
View 6 Replies
View Related
Sep 28, 2007
Morning all,
Am trying to create a really simple form where the user can select one of five fields, an operator (just =, Not, <, and >) and the values are pulled from whatever field is chosen.
I have the form down and can populate the values list based on the field however now i need to use the values in these boxes to create a WHERE clause.
I'm really new to SQL and have only done a couple of online tutorials but i think im on the right lines?
DoCmd.RunSQL "SELECT * FROM qryLinkMasterPrimary WHERE " + cboFields.Value + " " + cboOperator.Value + " " + cboValues.Value
Is this even slightly close? I've tried searching the internet and everything i find is waaaaay too complicated for me to get my head round.
Any help is greatly appreciated
Mike Harkess
View 14 Replies
View Related
May 2, 2013
I have a form - RunQuery And I have a query - Q2.
The form has text boxes for dates txtStart2, txtEnd2.
On button click I have a macro.
Query Name Q2
view Datasheet
datamode edit
The query will open
The query has a field Assigned which is Date/Time
In my query design view I have this as the criteria:
>[Forms]![RunQuery]![txtStart2] Or <[Forms]![RunQuery]![txtEnd2]
I also have filter on load set to yes.
However the filter does not seem to work.what is missing or wrong?
View 6 Replies
View Related
May 15, 2005
i am trying to create an append query that gets 2 values from one table and a 3rd value from a form.
It gets the values from the table fine but always gives me a popup asking for the value from the form.
I have used the criteria build... and selected the loaded form and the field value that I am looking for. I have also made sure that there is a value in the field on the form.
is there a reason why the query will not grab the number in the forms field? or am I jsut not doing this right.
I have done this many times on a combo box and it works fine. It is jsut not working for a field that is typed in
Can anyone help me?
View 5 Replies
View Related
Nov 12, 2005
Hi,
I have a popup form called Labor that is working perfectly fine, what i mean is that it is getting populated with data from the query I built using a single table called PrimaryBid_Master. However I have had to add many fields to this table before invoking a query on it, now I'm running close to the 255 field max limitation.
So I have created a new table called Labor_Primary with all the fields that I need and took them out of the PrimaryBid_Master table freeing up about 50 fields.
The query I made combines 2 tables: table1=PrimaryBid_Master table2=Labor_Primary, they are linked by a common field called invoice#.
But now when Im in the PrimaryBid_Master form and click the command button to go to the popup form Labor non of the values that were populating the popup form from the primaryBid_Master form work, they were working fine the only thing I changed was the popup forms record source to the new query I built.
Here is a print screen of the new query I built using 2 tables, and I also show the old query that works fine using 1 table. http://www.roofmart.net/query1.asp
Maybe you can see some thing I did wrong; do I need some type of filter to fetch the fields? When I open the new query there is no data/values in it.
Thanks--I appreciate any help.
View 1 Replies
View Related
Mar 21, 2006
I have a form with 4 different search fields used to query a table and limit results: Policy#, SSN, Credit_Amt, Debit_Amt. Any matching rows based on the search criteria are returned in a subform.
My problem is how to handle any fields where the user doesn't specify a search value.
In my query, I have the Criteria set to:
Field: Policy_No
Criteria: =[Forms]![form name]![Policy#]
Field: SSN
Criteria: =[Forms]![form name]![SSN]
and so on....
If I place my criteria on different OR lines, the query runs, but if a user specifies more than 1 criteria, I get all the data for one criteria(such as policy# matches) and all the data for the second criteria(such as all the records for a certain credit_amt, regardless of Policy #).
If I place my criteria on the same criteria line, effectively making it AND, I get no results because I assume the database is looking for any blank search field values.
So, how do I make the query an "AND" query, but actually get results by ignoring any NULL search fields? So if a user enters a Policy# and Credit_Amt, the results will be only matching rows by Policy# AND Credit_Amt, and doesn't look at SSN and Debit_Amt.
I've tried different iterations using IIF(Not IsNull(.... to no avail.
Thanks!
View 3 Replies
View Related
Jun 28, 2005
Hi there. What I'm attempting to do is pass multiple values from a multiple selection list box as criteria for an Append Query. Is there any way to do this? The DB keeps track of Real Estate boards and the forms that they use, I would like to be able to select all the boards in a given state/province, but have the ability to deselect some within that province if I don't need them in the query (this is the criteria i'm trying to pass). I don't need to use a list box, any control that would allow me to pass multiple values would be great. Anyone able to help? Thanks very much.
View 1 Replies
View Related
Oct 20, 2005
I'm currently working on a database which provides copier and postage activity in my department, using Access. I'm trying to debug a query which is called by a form that accepts input for a particular month and year. Whenever the OK button is clicked, it gives a message "The Microsoft Jet database engine does not recognize 'Combination Query' as a valid field name or expression."
"Combination Query" is a union query which calls two intermediate queries, each of which in turn calls a base query. Only one, the query responsible for gathering copier activity, is a crosstab query.
The problem I believe lies in either or both of the base queries only when the form is used. When I use a regular parameter query, I receive no error messages.
Here is the SQL code I used for each:
Query 1: Copier Activity
Code:PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long;TRANSFORM Sum([big table].Copies) AS SumOfCopiesSELECT [big table].[Copy Code], [User codes].Alias, Sum([big table].Copies) AS [Total Of Copies]FROM [User codes] INNER JOIN [big table] ON [User codes].[Copy Code] = [big table].[Copy Code]WHERE (((DatePart("m",[big table].[Time]))=[Forms]![frmObtainMonthlyInfo]![cmbMonth]) AND ((DatePart("yyyy",[big table].[Time]))=[Forms]![frmObtainMonthlyInfo]![cmbYear]))GROUP BY [big table].[Copy Code], [User codes].AliasPIVOT [big table].[Copier Location];
Query 2: Postage Activity
Code:PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long;SELECT [big Postage Activity].sAcctNum AS Account, Sum(Nz(cBaseRate*lNumPieces,0)+Nz((cRegisteredFee+ cCertifiedFee+cReturnRecFee+cReturnRecMerchFee+cSp ecDeliveryFee+cSpecHandlingFee+cRestrictedDelvFee+ cCallTagFee+cPODFee+cHazMatFee+cSatDeliveryFee+cAO DFee+cCourierPickupFee+cOversizeFee+cShipNotificat ionFee+cDelvConfirmFee+cSignatureConfirmFee+cPALFe e+cResidualShapeSurcharge)*lNumPieces,0)) AS TotalFROM [big Postage Activity]WHERE (((DatePart("m",[big Postage Activity].[sSysDate]))=[Forms]![frmObtainMonthlyInfo]![cmbMonth]) AND ((DatePart("yyyy",[big Postage Activity].[sSysDate]))=[Forms]![frmObtainMonthlyInfo]![cmbYear]))GROUP BY [big Postage Activity].sAcctNum;
I edited this to make it look similar to a recommendation posted on another forum, but sometimes I would get the message "The Microsoft Jet database engine does not recognize '[Forms]![frmObtainMonthlyInfo]![cmbMonth]' as a valid field name or expression."
I also included PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long; as the first line in the intermediate tables, but I still receive the same error message.
I'm wondering if there was something I overlooked or if there was a way around this. I can post the other queries if you need those. Many thanks in advance for your help.
View 3 Replies
View Related
Oct 16, 2013
I have a form that has a dropbox list and I want the values of the form to change according to the value in the dropbox.For example:
When the dropbox contains the value A,
The form would display x=1, y=2, z=3.
When the dropbox contains the value B,
The form would display x=4, y=5, z=6.
I managed to create a query that opens the QUERY's results according to the value in the dropbox but I can't managed to run the query in the form, return the values according to the query and display them in the form.
I searched the internet and found many options including the Dcount command but I don't think it fits my case..I know I have to write something in the AfterUpdate field of the dropbox but I just can't figure out what it is...
View 8 Replies
View Related
Dec 17, 2013
I have a database and on the main table one of the columns is "Status". The values in that column can be either "Worked, Unworked, or Overdue".
I would like to be able to create a form so that the user can run a query and select which Status' they want to see. For example maybe they only want to see Unworked and Overdue so they can select those and run the query.
View 4 Replies
View Related
Nov 9, 2007
Hi, I am a complete idiot with VB/SQL/*anything for that matter*
Made an unbound form to search and retrieve records from a query (parameter query) (for property listings)
I've got the following fields in my "Search_Form"
1) Project (combo box pulls data from projects table)
2) Size (from - to)
3) Rate (from - to)
4) Date (from - to)
5) Price (from - to)
I've set variables for each field .... VAR_sizeFROM....... VAR_sizeTO etc.
Now here's my problem.
1) How do use VB to send the user inputs from my "Search_Form" to "Search_Query". And is it possible for either of the search fields to be blank in the form?
2)How can I do the same for a report?
Please help, I've been stuck with this for some time..
Thanks in advance
View 5 Replies
View Related
Oct 29, 2014
I have 2 form controls one a combobox and the other a text box. The text box select the site (txtLocation) where the user can enter part of the name of the site and all sites with those characters are returned. I've done this by adding:
Code:
Like [Forms]![frmSearchDB2]![txtLocation] & "*"
into the criteria on the Site field in the query design editor.I also want the combobox to have an affect on the query. I want it to query on user status. However if the combobox reads "All Users" I want it to return all status's and all null values. In the criteria field I put:
Code:
iif([Forms]![frmSearchDB2]![cbxUserStatus] = "All Users",like "*",[Forms]![frmSearchDB2]![cbxUserStatus])
It kind of works but no null values are pulled back. Should it be an expression?Do I need to do it in VBA?
View 1 Replies
View Related
Feb 26, 2014
I have a table listing about 20 elements as field names eg FE, CR, NI, TI and so on.
I have built a form which has a combo box listing these elements by selecting "fields" in the property settings of the combo box & next to this combo box i have 2 text box's where the user can input Min & Max values to pass on to the query.
E.g., FE (chosen from the combo box) value between (Text box1) and Text box 2.
I can run the query to give me values between the 2 text box's by using the following formula in the criteria (Between textbox1 and textbox 2).
The issue i have is to be able to select the element from the listbox, input the min & max values identified and be able to pass this to a query so the query can filter based on the field and values passed?
View 3 Replies
View Related
Aug 7, 2013
I have a database in Microsoft access 2010. The database has a table that stores prospective customer records, and a form that is used to input a search criteria(s) via textboxes, which then queries prospective customers table and returns the records that contain the inputted search criteria(s).
An Example of Textboxes values on the search criteria form:
Textbox - name: bob
Textbox - address: Left blank
Textbox - category: car,boat,truck
I Have tried creating a query with the following
field: name
criteria: like “*” & name & “*”
field: address
criteria: like “*” & address & “*”
field: category
criteria: like “*” & category & “*”
SQL code:
SELECT customerName ,address,category
FROM prospectiveCustomers
WHERE customerName LIKE “*” & name & “*” AND address LIKE “*” & address& “*” AND category LIKE “*” & category& “*”;
That works, but only for one value in a textbox. Once there is more than one value in a textbox (e.g name: bob,smith), the query returns no records.
I have also tried splitting the values using the comma as a delimmter, then inserting the values into a new table. That is fine until one of the search criteria textboxes has been left blank. So the query I created will run, but returns no records.
SQL CODE:
SELECT prospectiveCustomers.name, prospectiveCustomers.address,prospectiveCustomers. category
FROM prospectiveCustomers, [SearchCriteria-name], [SearchCriteria-address],[SearchCriteria-category]
WHERE prospectiveCustomers.name Like [SearchCriteria-name].name AND prospectiveCustomers.address LIKE [SearchCriteria-address].address AND prospectiveCustomers.category LIKE [SearchCriteria-address].category;
View 2 Replies
View Related
