Criteria Text Length Issues With Query
Oct 13, 2005
Hi All,
I've been developing the code below but when the length of .lstAnswers is longer than 128 it doesn't delete the question.
Is there a reason under queries or SQL this should happen? I'm hoping that i'm just thick and there's an easy answer but nothing seems to work.
--------------------------------------------------------------------------
Private Sub cmdRemoveAnswer_Click()
Dim Answers_SQL As String
Answers_SQL = "DELETE tblOptions.* " & _
"FROM tblOptions " & _
"WHERE (((tblOptions.strAnswer)=[Forms]![frmQuestionWizard]![lstAnswers]) AND ((tblOptions.strQuestion)=[Forms]![frmQuestionWizard]![lstQuestion])) OR (((tblOptions.strAnswer) Is Null))"
DoCmd.RunSQL Answers_SQL, 0
lstAnswers.Requery
End Sub
--------------------------------------------------------------------------
View Replies
ADVERTISEMENT
Jul 19, 2006
Hi there,
I'm working on a select query that I have created through the visual query design window in Access 2003, I have multiple criteria but the query is not filtering the results properly.
I'm selecting only records :
where the DeliveryCountry field is within the EU - works
where the ProductID does not contain Repair, Upgrade or Rpr - doesn't work
where the Demo/Sale ID is 2 - works
I've viewed the code in SQL view but I still can't locate the problem, though I suspect it lies in the treatment of ANDs and ORs.
The SQL is pasted below (I have reformatted with indentations so I hope it is easier to read).
One thing to note: the reason the countries list is split in two is because Access did this automatically when I had one long list. Is there a maximum length for WHERE clauses?
Thanks in advance for your help!
Abby
SELECT Orders.ShipDate, Products.[Standard Tarriff Number], [Order Details].[Quantity]*[Order Details].[unitprice]*(1-[discount])*(1-[special discount]) AS [Line Total], [Order Details].Quantity, Orders.OrdDeliveryCountry, Orders.OrderID, [Order Details].ProductID
FROM Products RIGHT JOIN (([Demo/Sale] RIGHT JOIN Orders ON [Demo/Sale].[Demo/SaleID] = Orders.[Demo/SaleID]) LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")
AND (([Order Details].ProductID) Not Like "*Upgrade")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")
AND (([Order Details].ProductID) Not Like "*Repair")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")
AND (([Order Details].ProductID) Not Like "*Rpr")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden")
AND (([Order Details].ProductID) Not Like "*Upgrade")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden")
AND (([Order Details].ProductID) Not Like "*Repair")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden")
AND (([Order Details].ProductID) Not Like "*Rpr")
AND ((Orders.[Demo/SaleID])=2))
ORDER BY Orders.ShipDate DESC;
View 5 Replies
View Related
Aug 25, 2005
is there a way to allows more that 255 chars in to a text field in a mdb? or do i have to do something like save it to a test file then link to the text file (cos i really dont know how to do that :P)
View 3 Replies
View Related
Jul 9, 2013
I'm trying to apply a custom input mask to a text box control. The text box must contain a 43 character string, broken into sections as follows :
AAAA-AAAAA-AAAAA-AAAAAA-AAAAAAAAA-AAAAAAAAAAA-AAA
(The format is quite strict and always comprises alphanumeric characters , hence I figured an input mask would, if nothing else, support the user to key the string accurately from its constituent parts...)
However, when I try to apply the input mask to the text box, I seem to run out of characters - I get as far as the below but the input mask wizard won't let me extend the mask to the final 8 characters
AAAA-AAAAA-AAAAA-AAAAAA-AAAAAAAAA-AAAAAAAAAAA-AAA
Are input masks restricted to 35 characters in length? And if so, how else could I make this, admittedly complex, text input more manageable for end users?
View 2 Replies
View Related
Sep 26, 2014
I have 2 fields in a query which I want to write to a table. Eg ProductName (field length 10) and Product Code (field length 5) the fields need to be filled out so the length makes the maximum text
For example product name 'test' length is 4 i have to add 0 to make the length to 10 so the field will be 000000test same thing applies to Product code. I have 700 records like this, is there a way in VBA where it adds the extra text to make it the maximum length.
View 14 Replies
View Related
Jan 19, 2007
Hi,
I am having trouble importing text records into a simple Access 2002 database. I’d be grateful for any help; it would save me hours and hours of manual cut-and-paste from Word into Access!
The records I want to import are text passages which originate in Word. Within some records you might find more than one paragraph. I want that formatting preserved. I signify the end of a record with a “#” character.
I create an Access database consisting of two fields: a) one called "Item" for the text records, with the data type set to memo so it can handle long text passages, and b) one for the primary key ID, which when prompted I let Access create automatically.
Here’s what happens when I try to import the data to my “Item” field”. First, I cut and paste all my original text from Word into Notepad. Then in Access I go File / Get External Data, and then choose Import, and choose Text as the data type. In the ensuing wizard under the Advanced tab I choose the option for delimitation by a specified character, and make “#” that character, and I speciy that I want the data to go into the "Item" field as memo data type. I then finish the wizard.
Sometimes I get error messages that not all data imports successfully, due to key violations, and when I check, nothing has been imported. Other times I just get a message saying it didn't work out. It's never worked.
Here are the records I'm trying to import from Notepad:
Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
#
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
#
ccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccc cccccccccccccccccccccccccccccccccccccccccccccccccc ccccccccccccccccccccccccccccc
cccccccccccccccc
#
ddddddddddddddddddddddddddddddddddddddddddd
#
(end of records)
I've tried two other forums but none of the suggestions work. There must be some way to do this other than by manual cut and paste. Any advice?
View 1 Replies
View Related
Jul 28, 2014
I have a report that prints labels (similar to avery labels) which pulls data from a query. These labels are all unique and vary in length. Due to the length variance, I want the font size to get smaller for labels with more characters. I want the text to go down to another line when necessary.
In my report under the OnPage Event Procedure I wrote this:
Private Sub Report_Page()
If Len(Text2) > 20 Then Text2.FontSize = 8
If Len(Text2) > 10 And Len(Text2) < 21 Then Text2.FontSize = 10
If Len(Text2) < 11 Then Text2.FontSize = 14
End Sub
This works to some degree but the problem is that once there is a longer label, all of the proceeding labels are resized & the longer label that should have been resized was not.
Is there another place I should be writing this code? I looked under events for the textbox but there doesn't seem to be anything that would work since none of them would trigger when printing labels
View 3 Replies
View Related
Aug 6, 2014
I have a table with about 300,000 records. About ten fairly small fields per record. I am trying to change the length of a text field from 25 to 40 characters, and I get the error message, 'MS Access can't change the data type. There isn't enough disk space or memory'.
I have never seen this message before. I have about 64 Gig of free disk space. What can I do?
View 4 Replies
View Related
Dec 10, 2006
Hi, How can I make the data in a textbox on my form automatically entered as the search criteria in a query. So say the box says Mike Johnson, can I make a command button (That I can eventually subsititute as the actual box) that puts the Nma emIk Johnson into a search query and brings up all the information on him from the database?
View 4 Replies
View Related
Oct 15, 2007
I have form that will pop up to run a report, and allow the user to select a state for that report.
There is a combo box which you can select a state, then based on what state it is, it puts a criteria value into an unbound text box on the form.
The query has this set as the criteria:
[Forms]![frmStateSelection].[txtStateFilter].[Value]
I have also tried just:
[Forms]![frmStateSelection].[txtStateFilter]
The value of the text box is:
Like '5???????'
When the query is run, it returns 0 results. However, if I put in Like '5???????' as the criteria in the query itself, it runs properly.
I tried closing the form and running the query so that it asks me for a value since it can't find that textbox since the form is closed. I pasted in Like '5???????' and it returns 0 results.
I have used this method for entering start and end dates for reports before and it has worked just fine. I can't figure out why it isn't working here. Any thoughts?
View 13 Replies
View Related
Oct 25, 2006
Simple question but I've been stuck for a looong time.
So what I want to do:
1. enter a value into a text box (Home_Tel) in a form (frmStudentClass)
2. which runs a query (qryNameTel)
3. and return the results to a combo box (Student_Name) on the same form.
The same Home_Tel may have several Student_Name results.
-I have entered
[Forms]![frmStudentClass]![Home Tel]
for the criteria in the query
- However I can't get the results to turn up on the combo box
- I have the following:
Private Sub Home_Tel_AfterUpdate()
' run query
DoCmd.OpenQuery "qryNameTel"
Me.Student_Name.Requery
End Sub
Any ideas?
Or is there an easier way to tackle the problem?
Home_Tel and Student_Name are from the same tables.
I have created a query just for Home_Tel and Student_Name
Thanks everyone!
View 1 Replies
View Related
Feb 26, 2014
I'm trying set the query criteria from a textbox on a form. It's a Status field in the query. 1 = Open, 2 = Closed. If I set the criteria to "1" it shows all open, "2" and it shows all closed and "1" or "2" it shows all. The problem I'm having is setting these in the form. I've set the query to pull the value from the form. I can get the Open or Closed to work but not the All. My textbox shows exactly how the criteria should read "1" or "2" but doesn't show any results.
View 3 Replies
View Related
Aug 31, 2014
I've got a continuous form based on a query.Each of the fields have a search box below it (in the footer) which should ideally filter the query.I'm starting with the FirstName field.In the form's query, I've set the criteria to the following for the FirstName field:
Code:
Like "*" & [Forms]![frmStudentDetails]![txtSearchFirstName] & "*"
frmStudentDetails is the form name.
txtSearchFirstName is the search box's name (in the footer).
The AfterUpdate event for txtSearchFirstName is:
Code:
Me.Requery
But, it doesn't work; when I switch to form view, it displays an error. The Microsoft Access database engine does not recognize '[Forms]![frmStudent Details]![txtSearchFirstName]' as a valid field name or expression.
View 12 Replies
View Related
Sep 10, 2007
I sure am having difficulty with this. I'm trying to have the criteria in my form refer to a text box within one of my forms which is the text as I would write it myself within the query design view.
Query's [DEST_ZIP5] criteria = [Forms]![Mainswitchboard]![QueryWizard].[form]![calcDestZip5]
[calcDestZip5]'s value in QueryWizard form = "55422" OR "55343"
Basically, I just want to be able to enter the text string within a form instead of within the query. Sure this can be done!
Oh, and for what it's worth...
If, [calcDestZip5]'s value in QueryWizard form = "55422"
Then the query runs just fine. It's as if the query doesn't want to accept multiple criteria from another source.
TIA
View 5 Replies
View Related
Dec 11, 2013
I have a totals query that provides an avg for each month. i'd like to be able to use a text box control (named "Date") on a form (named "Report Runner") to show only a certain month and it's avg.
I tried using this as criteria on the "MonthGroupPMC" field:
Code:
Format([Forms]![Report Runner]![Date], "yyyy-mm")
but the results came up blank.
how can i filter the results of this query to show only one month, specified by the [Forms]![Report Runner]![Date] control?
View 5 Replies
View Related
Dec 12, 2014
Am not getting a value from a form text box when using in the criteria line in my query. Am referencing like [Forms]![FormName]![FormLabelName]. If I copy the data in the form and paste it into the query, it works fine, but if I just reference the form, I get no results.
View 13 Replies
View Related
Jun 5, 2015
I 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 = "__/__/____"
View 3 Replies
View Related
May 7, 2014
I've been writing queries in the following format for years in Access 2003, but having recently transistioned to Access 2010, I've found the following sql doesn't work.transform
sum(s.value)
SELECT
s.sn, s.ln, s.pn, s.id, s.lat, s.point, s.supply_type, s.used, s.real, s.code, c.name
FROM
supply_points s
, codes c
WHERE
s.code = c.code
and s.id is not null
and s.code = 1075
and s.month >=[forms]![main]![gppstart ]
group by
s.sn, s.ln, s.pn, s.id, s.lat, s.point, s.supply_type, s.used, s.real, s.code, c.name
pivot
s.month
In Access 2010, this query returns the following error message:the Microsoft Access database does not recognize '[forms]![main]![gppstart]' as a valid field name or expression
Is this a common phenomena in Access 2010?
View 3 Replies
View Related
Jul 4, 2005
What is the maximum number of characters which can make up a query?
Is it 255?
J-F
View 2 Replies
View Related
Dec 18, 2013
I am trying to run a query and display the results in a report (the report side of it is childs play and not a problem). The problem I am having is that I have a search form which should allow the user to search any one of 6 fields (text boxes) or a combination of each.
If the user enters something into a field then that search criteria must match. I wanted to have it so if all fields are left blank then it will show all entries in the database (but it isn't, it shows a blank report). I also wanted it to allow partial completion of boxes.
So for instance if I have 5 customers (Jones, Jonson, Jonus, jimjonkins, Janis) and I type "Jon" into the name field then I would like it to show the first 4 records as they all contain "jon" somewhere in their name but its not, its only allowing exact matches.
I currently have '[forms]![Search_Customer]![Search_Name]'.
View 2 Replies
View Related
Feb 9, 2006
I have a query in which I need to pull all values from a field that is exactly 10 characters in length (Alpha or Numeric). Can anyone help me with this?
Thank you!
View 3 Replies
View Related
Jan 22, 2007
In a database that holds membership records, I have three years worth of info relating to membership dues paid. I want to extract who has not paid a membership fee for the last three years, so we can strike them off our records, i.e. I have members' personal details, but there are no records in the payments table, nothing has been entered because they just have not paid. Do I run a query looking for a zero length string? Just how do I achieve this? Have I done right in not entering anything?
Help appreciated!
View 2 Replies
View Related
Jul 12, 2007
I want to create an query that will update a field value for records where the length of the field value is less than a given number (if LEN of UniqueID is less than 6 for example), but I can't figure out how to write a SELECT query, much less an UPDATE query for this condition....can someone help me, please?
View 1 Replies
View Related
Apr 15, 2014
I'm trying to pull a date from a table into a text box on a form tab control using DLookup and I just can't figure out what is wrong with my DLookup expression:
=DLookUp("DateOrdered","tDateOrdered","PrNumber=" & [PrNumber]) ----- (DateOrdered is short date, PrNumber is text, db is split Access 2013)
Whats missing in this expression? I've tried every criteria variant I could find but to no avail.
View 9 Replies
View Related
Mar 13, 2007
OK, I have been searching around on the fourm for over an hour now...I give up. If this is some where else, I am sorry.
I have a database of maintenance data. There are several columns that are usually filled in, some records have some columns blank. They are formated text because they hold letters and numbers (see pic).
I am using a form to query the table...no problem. The form has text boxes the user filter down the data
The problem comes are with the results of the query. Any record that has a blank column is not retuned. I am using "Like" so that the user can enter in partial codes. I know "like" won't return "null" records.... Help!
Things I have tried:
1)IIF(form field is blank, return table field, else use like command to filter) - returns nothing!
2)Like "*" & [Forms]![Fleetwide_data_Request]![MAL_CD] & "*" returns all records without blanks (i.e. missing data)
what else can I do?
Thanks
View 5 Replies
View Related
Oct 21, 2006
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Any help would gratefully be appreciated. Thanks
View 5 Replies
View Related
