Do I Need Zero Length String Query
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 Replies
ADVERTISEMENT
Aug 19, 2005
I have a fax number field iwhich has a mask. +(000)-00-000-000 in a table of many records.
The problem is that every record has a number in the first part of country code +(001)which must have come there by mistakes, few records have complete and true fax numbers.
I need a query that can Update this Fax_Number field where the length of the string is less than 7 digits.
Some thing like UPDATE Contacts SET Contacts.Fax_Number = ""
WHERE ((Len("Fax_Number")>"6"));
How can i do this. Thanks.
View 4 Replies
View Related
Oct 18, 2006
How can I extract a string and from a textbox and make out that it is x (here x can be any value, e.g. 15) characters long??
View 1 Replies
View Related
Jan 26, 2007
I am using VBA code to export a small table as a .csv file, which is no problem, except that the customer insists that where a field is blank, the .csv file must contain a pair of double quotes.
In other words, I can produce this:
"Field1","Field2",,"Field4","Field5"
but they insist on this:
"Field1","Field2","","Field4","Field5"
In the table design, I have made the default for each field = "", but I still can't seem to force the double quotes into a blank field.
Any suggestions will be welcomed.
View 2 Replies
View Related
Aug 16, 2005
Heyo, thought I'd toss my line to see if anyone has had a corruption like this:
Today I went digging through some of my tables on a db I've been building and noted that most (but not all) of my text fields had Unicode Compression/Off and Zero-Length-Strings/Allowed. Now, I know for certain that I never touch Unicode Compression and zero-length strings are not my bag in this db so I went to toggle these and I recieved 'Access encountered an error when attempting to save' type error when I, you guessed it, attempted to save.
So I built fresh tables, and imported my data, and all seemed well but digging further through my database I have noted that ALL of my tables seem to be suffering from this in some way.
First off, does anyone know what has happened?
Secondly, does anyone have a guess as to why this happened?
Lastly does anyone have a suggestion on how to rectify this?
I'm not a huge fan of building this entire gigantic db from the ground up and I'll do it if I need to, but I'd prefer to figure out what went wrong before we get there and it happens again.
Many thanks,
~Chad
View 1 Replies
View Related
Mar 14, 2015
I have strings of names in a calculated field named [CurrentDelReverse] that are separated by spaces, but some strings have 2 names and some strings have 7 names.
They look like this:
tjcrane becca002
shiel001 donohue ekaler
jpohl jhalling jsleifer laerwin chan ekaler
I need to separate the names, but I only need the first two names in two separate fields.
I am able to capture the first name with no problem using:
Delegatee: Left([CurrentDelReverse],InStr([CurrentDelReverse]," "))
My problem is trying to grab the second name in the list to show in a new column.
I have tried so many different things and the closest I got to solving it was:
Delegator: Trim(Mid([CurrentDelReverse],InStr(1,[CurrentDelReverse]," ")+1,IIf(InStr(InStr(1,[CurrentDelReverse]," ")+1,[CurrentDelReverse]," ")=0,0,InStr(InStr(1,[CurrentDelReverse]," ")+1,[CurrentDelReverse]," ")-InStr(1,[CurrentDelReverse]," "))))
This returned the second name in the list but ONLY if it had more than two names in the string.
View 7 Replies
View Related
Jun 11, 2014
I have a continuous form that displays data from a table. What I am trying to accomplish is to change the format of the field if the string length is equal to 11, to "@@@-@@@-@@@@-@". Otherwise if it is greater or less than 11 then no format.
I have tried it using code:
If Len(Me.FormFieldName)=11 Then
Me.FormFieldName.Format = "@@@-@@@-@@@@-@"
End If
Is it even possible and if so is my syntax correct or am I way off base?
View 8 Replies
View Related
Sep 16, 2013
I have a table where I want to break longer strings into three-character strings.
So... for example the data item "Forest," I'd like to generate "FOR" "ORE" "RES" "EST" and write all four to another table, allowing for duplication. Alternately, we could make a list and just count the unique 3-character strings. The end goal here is to measure the frequency of 3-character strings across a set of data.
how to do this with an Access query?
View 1 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
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
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 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
Jun 7, 2013
MS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
View 13 Replies
View Related
Jan 3, 2008
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?
View 7 Replies
View Related
Mar 12, 2007
Hi all,
I was looking for some help. I am trying to setup a table with a field for web address. People are entering www.website.com etc however I need them to make sure it starts with http:// Is their any way I can put validation on the field to make sure that this is entered? Or maybe I could use an input mask?
Any suggestions would be gratefully recieved.
Andy.
View 3 Replies
View Related
Jun 5, 2007
Hi all,
My name is Sarah and I'm working with a simple guestbook script that runs with an Access database. Right now the script displays all guestbook entries. I want to alter it so it only displays entries if a given person's name is "Bruce". This might sound goofy but if I can get this functionality then I'll be able to alter the script to build a bunch of simple scripts that I have planned.
Here's the code that displays all entries on the guestbook:
Code:<%'Dimension variablesDim adoCon 'Holds the Database Connection ObjectDim rsGuestbook'Holds the recordset for the records in the databaseDim strSQL'Holds the SQL query for the database'Create an ADO connection odjectSet adoCon = Server.CreateObject("ADODB.Connection")'Set an active connection to the Connection object using a DSN-less connectionadoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("guestbook.mdb")'Set an active connection to the Connection object using DSN connection'adoCon.Open "DSN=guestbook"'Create an ADO recordset objectSet rsGuestbook = Server.CreateObject("ADODB.Recordset")'Initialise the strSQL variable with an SQL statement to query the databasestrSQL = "SELECT tblComments.Name, tblComments.Comments FROM tblComments;"'Open the recordset with the SQL query rsGuestbook.Open strSQL, adoCon'Loop through the recordsetDo While not rsGuestbook.EOF'Write the HTML to display the current record in the recordsetResponse.Write ("<br>")Response.Write (rsGuestbook("Name"))Response.Write ("<br>")Response.Write (rsGuestbook("Comments"))Response.Write ("<br>")'Move to the next record in the recordsetrsGuestbook.MoveNextLoop'Reset server objectsrsGuestbook.CloseSet rsGuestbook = NothingSet adoCon = Nothing%>
My friend told me that I need to alter the query string to something like this, but I can't get it to work:
Code:SELECT tblComments.Name, tblComments.Comments FROM tblComments WHERE tblComments.Name = Bruce ;
As you can tell I'm not a programmer but I can usually alter scripts to get them to do what I want. If I could get this figured this out I would be the happiest woman alive.
Sarah
View 2 Replies
View Related
Mar 5, 2008
i have a string in VB (eg. "test")that will cahnge depending on the input, ihow can i search for the string and display the data into a form?
i currently have this
"SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete, [Tasks].Tracking_Number FROM [Tasks] WHERE [Tasks].Staff_Name like [forms].[Main].[test] ORDER BY [Tasks].Staff_Name;"
but how can i make it so it spits the data into its corresponding feilds onto a form? (just like it would be if i performed a wizard?)
View 1 Replies
View Related
Jul 4, 2005
I need to build up a string containing values separated by commas. The following almost does this.
I use an update query in which the "Update to" field for 'String field' contains
[String field] & IIf(Len([String field])<1,"",", ") & [Related table]![Value to append]
I would like to have the [Related table]![Value to append] in ascending order.
I.e. I would like the result to look like "A, B, C, F, H", not "B, C, H, F, A". Any suggestions as to how to apply the sort?
View 1 Replies
View Related
Nov 22, 2005
Hey,
Can someone pls tell me how can i get data from query to a string so i can use it in my VBA code?
I have a query that basicly creates me a uniqeu code and i need that code when i write new record in datasheet form? For example:
I have Query1 that has a field1 with data "AASSFF"
Now i need that data in my Table1 field1
THX, S
View 1 Replies
View Related
Mar 30, 2008
Hey people,
This should be any easy one to solve:
I need to split a field in my query into two separate fields, e.g fullname into a first and last name, so "John Smith" will appear in two columns, "John" and "Smith".
Thanks in advance.
View 3 Replies
View Related
Aug 8, 2006
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"
View 4 Replies
View Related
Jun 23, 2005
Hello, I have a query that returns a list of courses undertaken by certain employees. They are in the format NPTC CS 30; NPTC CS 31; NPTC 33; NPTC FTC 38; NPTC FTC 40 etc...
Anyway what I need to do with this is convert these results to a string and manipulate them in such a way that they can be displayed in a text box on the back of an ID card in the following format:
NPTC CS 30, 31, 33, NPTC FTC 38, 40... etc.
Because there is not enough room on the back of the ID card to use a subform to just display them in a list...
View 3 Replies
View Related
Sep 8, 2005
In MySQL I can use the LEFT(3, field_name) command, but I essentially want access to pull all records from a table where the the first three digits of a number field match those pre-selected by a form drop down.
This database is in Access 97. Is there a way to compare the first x number of digits using SQL only, or do I need to run each line through code first, and then check it (I really don't want to do that)?
example numbe rin field:
123456789
SQL checks to see if 123 matches value selected on a form.
View 1 Replies
View Related
Mar 8, 2006
Let's say I have a query like:
select [x]+[y] as A, [z]*[A] as B from MyTable.
I have found that sometimes this works and sometimes it doesn't, (Like, it works the first time I run it, but later quits working....)so I've gotten into the habit of repeating my calculations and writing it thus instead:
select [x]+[y] as A, [z]*([x]+[y]) as B from MyTable.
However, I now have some query fields that have so many calculations in them that I exceed the string length that Access allows for a query field. So is there a way to make the shorter SQL work consistently?
Any ideas appreciated.
Thanks!
PS: Here's the field I need to shorten:
AdjFcstDay: IIf(((Sum([AvgDaily])*First([forecastweeks])*5+nz(First([backlog]),0))/(First([forecastweeks])*5))+IIf([wiptotal]<(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev])))-[wiptotal])/5,IIf([wiptotal]>(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev])))-[wiptotal])/5,0))<0,0,(Sum([AvgDaily])*First([forecastweeks])*5+nz(First([backlog]),0))+IIf([wiptotal]<(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev])))-[wiptotal])/5,IIf([wiptotal]>(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev])))-[wiptotal])/5,0)))
View 1 Replies
View Related
Mar 8, 2006
I have this query:
INSERT INTO 1_2_06 ( Issue_ID, Assigned_To, ListType )
SELECT Issue_ID, Assigned_To, 'QPT'
FROM QPTActions
WHERE QPTActions.Entry_Date<=DateValue('1/2/2006') And QPTActions.Close_Date>DateValue('1/2/2006');
The Assigned_To values are a Lastname, Firstname format as they come out of the first database. But I need the comma stripped before they get entered into the INSERT database. Is it possible to do this on the fly?
View 2 Replies
View Related
Apr 8, 2008
Hello again.
I have a drop-down list that contains string values. Is it possible to construct a query that will sum the total number of times that the string value is selected? For example,
String Value Sum
================
Chicken 3
Beef 9
Pork 2
I have been trying to work on this for a while and cannot figure it out. Can someone shed some light on this issue? Thanks.
View 1 Replies
View Related
