Can i create a query that will have a string in the Criteria box, where that string is assigned a value in VB code?
for instance:
Dim xyz As String
xyz = "TV"
and I put in the Criteria Box WHERE xyz = "Pizza"
Hello,
I've scoured the dozen archived threads that relate to my challenge, but I still can't quite overcome it.
I have an aggregate query that runs but returns no values.
This is how I've tried to set things up:
A user makes a multiple selection in a list box on a form.
The multiple selection gets strung into an SQL that I feed to a hidden control on the form.
Whatever is in the hidden control then becomes the Where clause that I reference in a Criteria cell in my query.
The query is run when the user presses a command button.
Can anybody tell what's wrong with my code? Thanks very much in advance!
Private Sub cmdTractSelect_Click()
On Error GoTo Err_cmdTractSelect_Click
'Declare variables
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim qry As QueryDef
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String
'Assign values to the variables
Set db = CurrentDb()
Set tbl = db.TableDefs("City")
Set fld = tbl.Fields("Tract")
Set qry = db.QueryDefs("Param")
Set frm = Forms!frmTractSelect
Set ctl = frm!lstTract
strSQL = "[Tract]="
'Grab the list-box selection and string it into SQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " Or [Tract]="
Next varItem
'Make sure the user selected at least one tract
If Len(strSQL) = 0 Then
MsgBox "You didn't select anything" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
'Trim the SQL
strSQL = Left$(strSQL, Len(strSQL) - 12)
'Insert the SQL into a hidden control that will feed_
'the criteria of the Tract field
txtWhere.Value = strSQL
'Run the query
DoCmd.OpenQuery ("Param")
Exit_cmdTractSelect_Click:
Exit Sub
Err_cmdTractSelect_Click:
MsgBox Err.Description
Resume Exit_cmdTractSelect_Click
End Sub
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?
Hi,
I really hope someone will be able to help me with this one as I am sure im just missing something simple.
I have an unbound form which has 20 yes/no unbound check boxes. The purpose of the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so I have used the following criteria:
Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])
Which basically reads if field1 is no then display all records, else display all yes. Now that works fine but what I would like to have working is that if a client ticks field1, field2, and field3 it displays all records that have ‘yes’ in either field. Currently, if more than field is ticked the query treats it like:
Field1 And Field2 And Field3 And etc = true
I want to be able to select several check boxes and have the query return results for each check box that was checked. I would like to avoid doing this by having an append and delete query per checkbox.
Thank you
How to modify my query to accept parameters from a multiple choice list box. This is the SQL code behind my search query:
SELECT AircraftOperators.RegistrationNumber, AircraftOperators.PassengersNumber,
AircraftOperators.ManufactureYear, AircraftOperators.EmailToOperator, AircraftOperators.ExteriorPhoto,
AircraftOperators.InteriorPhoto, tblListOfAircraftOperators.OpratorName, tblAircrafts.AircraftType, tblAirports.AirportName, InfoSource.SourceType, tblCountry.CountryName, tblAircraftCategory.AircraftCategory
[Code] ....
I have got an unbound multiple list box called List44 (Row Source: query based on table tblAircraftCategory, Multi Select - Extended) that needs to be passing parameters to my main query called AircraftSearch2. The multiple choice list box have the following fields:
1. Piston
2. Turbo Prop
3. Entry Level Jet
4. Light Jet
5. Super Light Jet
6. Midsize Jet
7. Super Midsize Jet
8. Heavy Jet
9. Ultra Long Range
10. Helicopter
11. Air Ambulance
12. Cargo
13. Vip Airliner
14. Airliner
The user will use the form for selecting search criteria (the form is called SearchForm2 and has 5 combo boxes, 3 text boxes and one multiple choice list box).
I'm very new to access and need to modify (or coding a separate module) my query to include my multiple choice list box in my query?
Hi folks,
I have a query that returns about 3500 records (and runs very well I have to say). the issue comes when I attempt to export that queries results to a comma seperated text file. It gives me the message that the field is too small to accept the amount of data..bla bla bla... I've looked this up and it mentions stuff about memo fields and issues with that, but I don't have any memo fields in any of the tables that this is pulling information from. Does anyone have a clue why this would be happening...please help..this is urgent.
Thanks - J
Dear Access Gurus:
Help! I have creadted a basic foirm to collect data. The problem is when I switch to form view, the "add data, delete data" butons are disabled. I tab thorough the fields and I cannot add any data??????????????
I have checked and rechecked the data properties of the form tab and allow additions and allow deletions are set to yes.
I am at the end of the rope and I am completely lost. Do I have to reinstall Access?
Any help is most welcome..........please
Regards,
Dion
I have created a form based on a query that joins two tables. When I run the form, I am able to input new data into all fields that are from one of the two tables, but it won't accept new data into the second table. I don't get an error message that might help me identify the problem. The join between the tables is of the "include all records from Table A and only those records from Table B where the joined fields are equal."
Can anyone suggest where my problem might lie? Thanks!
kgm
I have noticed diagrams showing Microsoft SQL Server accepting SQL statements sent by “SQL Requesters” over connections. I would like to know if Microsoft Access can be used in that fashion?
Put another way, can Access be configured to accept an SQL statement that another program (e.g. a VBA program in a VBA enabled 3rd party app) creates?
Currently, my VBA program instantiates an Access database object and then manipulates it (I just add a record to one of the tables), then closes and destroys it. This solution seems fragile (it stops working—I can explain more if needed). I would like to know if the technique inferred by my question would be more reliable.
When using 'ww' as the criteria in the DatePart calculation it will not accept 1 for 6th Jan 2005 which is week 1, you have to enter 2 and it will select it, in fact all the weeks so far in 2005 are out by 1. To cure it you can add on -1 to the query and it works fine until you go back to the previous year and it screws up those dates.
Any ideas?
I've got a products table in which I have a field called weight. Obviously, this is to hold the weight of the item. I've it set to Number | Long Integer with desimal places set to 2. However, when I put 2.5 it makes it 2.
What could be causing this?
I have a db for my clinic with 3 tables, one is Patient (P_ID, P_Name, P_Age, P_Sex...etc. The second is Referral (R_ID, R_Date, R_Clinic, R_Diagnosis...etc. The third is Therapist (T_ID, T_Name, T_Division, TherapyDate, T_Plan...etc.
The main form contains all patient info and below I have a subform showing info from both other tables (Referral and Therapist).
Each patient could have more than one referral, so I made the relation between Patient table and Referral table, one to many.
Other relations are as follow:
Patient to Therapist (one to many) because one patient could be treated by different therapist each time he is referred.
Therapist to Referral (one to many) because one therapist could handle many referrals.
Now for the first patient, I entered the first patient info in the main form and then the referral and therapist info in the subform, then the same patient was referred to me for the second time, when I attempted to enter the second record (referral) in the subform I am not able to. Why I do not Know, I am sure I did something wrong.
By the way most of the fields in the subform are comboboxes except for dates.
I have a form that is based on a query that returns all the students taught by an indidual faculty member. There are two text boxes which I have added to the form bound to a table used in the query. However when I try to add data (attendance data) is doesn't accept it and simply "pings" each time I try.
View 3 Replies View RelatedI am trying to insert data into a table through a combo on a form. Originally, I had the text field size set at 15 characters, but the data I wanted to enter changed to 25 characters in length, so I changed the field size to 25. I then got the following message:
"The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."I increased the size, but still got the message. I even took the size right up to 100, but still got the message!
I have a table fed form that will not accept editing to the filter statement.
Currently the filter is [Dealer Name]='ABC Company'
This is causing a problem because we have multiple locations with the same Dealer Name. They do however, have different Primary Account numbers.
I thought to edit the filter to [Primary Account]=102
However, after I save everything and go back to form view, the filter reverts to the original.
I am using access 2007 but my tables does not accept data with 2 decimal places instead it is rounding it up. I have used all the formats stated in the property sheet but it does not accept the changes.
View 1 Replies View RelatedMS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
All,
I have inheirited a database which I am attempting to query via an SQL statement:
"SELECT * FROM MYTABLE ORDER BY SheetNumber;"
This query works fine under Access but when executed thru ADO 2.5 / Microsoft.Jet.OLEDB.4.0 from VB6 it reports the following error ("2147217833"):
"The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."
First things first, I'm not trying to add/insert/paste any data (see SQL statement above). Secondly, when I remove "ORDER BY SheetNumber" from the SQL statement the error does not occur. I need to retain the ability to sort and would be grateful for any help/suggestions.
For your reference SheetNumber is defined as a 20 char Text field in Access.
Thanks in advance,
Rob Black.
I am using Access 2003 and I am having trouble with a Combo Box on a form.
I had a combo box (based on a table), storing the choice in a field in the query and underlying table the form is based on. Everything was working fine, until... I altered the form based on a query. Now when you make a choice from the drop down list you get an error bell and nothing happens.
Before attempting to create a VBA User Defined Function that will accept arguments for creating UPDATE/INSERT INTO SQL statements, I thought I would check to see if some already exist. It seems like a very tough task to tackle. I'd like it to determine the data type of the the values being placed into the specified fields and subsequently provide the appropriate syntax. (i.e. '" & mString & "', #" & mDate & "#, etc.) Do any exist?
I'm working with MS Access 2003.
Thanks,
Steve G.
:confused:
Before attempting to create a VBA User Defined Function that will accept arguments for creating UPDATE/INSERT INTO SQL statements, I thought I would check to see if some already exist. It seems like a very tough task to tackle. I'd like it to determine the data type of the the values being placed into the specified fields and subsequently provide the appropriate syntax. (i.e. '" & mString & "', #" & mDate & "#, etc.) Do any exist?
I'm working with MS Access 2003.
Thanks,
Steve G.
I'm trying to resolve a problem with a form displayed as follows
MainForm
-->SubForm1 embedded in main form
--->SubForm2 embedded in SubForm1
When I edit subform2, then return to the mainform and edit a field on the mainform, I get an error.
'The data has been changed. Another user edited this record.... Re-edit the record.'
My understanding to resolve this is to do a requery. So I added:
Me.Parent.Requery
Me.Parent.Parent.Requery
as part of the LostFocus event for subform2. However, I now get a Write Conflict error when returning to a field in the main form. I get a dialog box with the option to Copy to Clipboard or Drop Changes. I can select Drop Changes and it will let me edit the main form afterwards.
This is Access 2010 over SQL. how to get the main form to accept the changes made to the underlying subform?
I currently have a drop down box with four options in there and the options are:
Low
Minimum
Medium
High
However when i change the options in the table to:
Low
Moderate
Significant
High
it comes up with the error message: the field is too small the accept the amount of data you attemtpted to add. Try inserting or pasting less data.
I do not understand what is wrong with the second option of words? The maximum for characters is set to 255 so i am not sure why it is coming up with that error message.
I try to put an expression for the property "bound column" so that my lookup values will be directly dependent on one of attribute's choice
I have got four options for my attribute. Each option will use a slightly different set of lookup values. I save all four sets in another table.
Then in my properties definition I refer directly to this table as lookup base. When I put bound column number equal to an expression so as to choose the right column of lookup values it refuses. It seems that it does notl ike anything other than a numeric value.
If that's a no-go, I am not sure how to achieve what I try to do then?
Essentially I have a table that 4 attributes: QID,itemID,CatType,Catchoice
CatType can have only 4 values. Each value will make Catchoice take on a different set of combo values
The problem I am having is that I am stuck at the Catchoice lookup definition because it is dependent on what kind of CatType the user picks.
Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:
05 Catagory A: 02 Product AA, 01 Product AB, 02 Product AC
10 Category B: 07 Product BA, 03 Product BB
04 Category C: 01 Product CA, 01 Product CB, 01 Product CC, 01 Product CD
etc...
I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.
I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?
I have the following query, which runs perfectly:
SELECT
[qry_BMO_District_Recap - RHS - Stage 2].Transit,
Format([qry_BMO_District_Recap - RHS - Stage 2].month,"mmmm yyyy") AS MonthOfYear,
[qry_BMO_District_Recap - RHS - Stage 2].DISTNAME AS District, [qry_BMO_District_Recap - RHS - Stage 2].Grouping_Name, [qry_BMO_District_Recap - RHS - Stage 2].Grouping_Priority,
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Count) AS M_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Amount) AS M_NtoB_Amount,
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count) AS Y_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount) AS Y_NtoB_Amount,
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Count) AS M_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Amount) AS M_BtoN_Amount,
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count) AS Y_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount) AS Y_BtoN_Amount,
[qry_BMO_District_Recap - RHS - Stage 2].AREANAME, [qry_BMO_District_Recap - RHS - Stage 2].Financial_Year
FROM [qry_BMO_District_Recap - RHS - Stage 2] LEFT JOIN [qry_BMO_District_Recap - LHS - Stage 2]
ON ([qry_BMO_District_Recap - RHS - Stage 2].AREANAME = [qry_BMO_District_Recap - LHS - Stage 2].AREANAME)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Month = [qry_BMO_District_Recap - LHS - Stage 2].Month)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Transit = [qry_BMO_District_Recap - LHS - Stage 2].Transit)
AND ([qry_BMO_District_Recap - RHS - Stage 2].DISTNAME = [qry_BMO_District_Recap - LHS - Stage 2].DISTNAME)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Name = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Name)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Priority = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Priority)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Financial_Year = [qry_BMO_District_Recap - LHS - Stage 2].Financial_Year);
When I try to reverse the above, as shown
SELECT
[qry_BMO_District_Recap - LHS - Stage 2].Transit,
Format([qry_BMO_District_Recap - LHS - Stage 2].month,"mmmm yyyy") AS MonthOfYear,
[qry_BMO_District_Recap - LHS - Stage 2].DISTNAME AS District, [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Name, [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Priority,
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Count) AS M_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Amount) AS M_NtoB_Amount,
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count) AS Y_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount) AS Y_NtoB_Amount,
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Count) AS M_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Amount) AS M_BtoN_Amount,
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count) AS Y_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount) AS Y_BtoN_Amount,
[qry_BMO_District_Recap - LHS - Stage 2].AREANAME, [qry_BMO_District_Recap - LHS - Stage 2].Financial_Year
FROM [qry_BMO_District_Recap - LHS - Stage 2] LEFT JOIN [qry_BMO_District_Recap - RHS - Stage 2]
ON ([qry_BMO_District_Recap - RHS - Stage 2].AREANAME = [qry_BMO_District_Recap - LHS - Stage 2].AREANAME)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Month = [qry_BMO_District_Recap - LHS - Stage 2].Month)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Transit = [qry_BMO_District_Recap - LHS - Stage 2].Transit)
AND ([qry_BMO_District_Recap - RHS - Stage 2].DISTNAME = [qry_BMO_District_Recap - LHS - Stage 2].DISTNAME)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Name = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Name)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Priority = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Priority)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Financial_Year = [qry_BMO_District_Recap - LHS - Stage 2].Financial_Year);
I get the error message "The field is too small to accept the amount of data you are trying to add. try inserting or pasting less data." I'm not attempting to run an insert or update query.
Access Help lives up to expectations by just repeating the error message, and the posts I can find on the forum which mention this error all seem to include something about a maximum of 254 characters in a field. None of mine are anywhere near that amount.
Does anyone have any idea what could be causing this?