limitation of characters in query?

I am trying to write a condition in my query using IIf statement and
the statement is quite long.
But I facing a problems that I am not able to type out all the
condition in the query.
Is there any limits for character type in the query field?

0
xiaodan86
4/10/2007 2:10:02 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1362 Views

Similar Articles

[PageSpeed] 51

On 9 Apr 2007 19:10:02 -0700, xiaodan86@hotmail.com wrote:

>I am trying to write a condition in my query using IIf statement and
>the statement is quite long.
>But I facing a problems that I am not able to type out all the
>condition in the query.
>Is there any limits for character type in the query field?

Yes: 1024 bytes if I recall aright. Help for "Specifications" will get the
straight scoop.

Big complex nested IIF statements usually indicate either a need for another
table, or an alternative approach. The Switch() function can do multiple IF
conditions; it takes arguments in pairs, evaluates the pairs left to right,
and when it first encounters a pair where the first argument is TRUE it
returns the second of the pair.

If it's a multiple value lookup - consider creating a simple translation
table. You can replace

IIF(Code = 1, "A", IIF(Code = 2, "B", IIF(Code = 3, "C", IIF....))))))))))))

with a simple table with two fields, Code and Result; join this to your table
by Code and display the Result.


             John W. Vinson [MVP]
0
John
4/10/2007 5:19:50 AM
Reply:

Similar Artilces:

Converting special characters
Is it possible to get the sum of cells using numbers entered in feet? I other words...I need to calculate a sum using numbers with the ' & symbol next to the number indicating feet & inches. Is there a special way to do this, or do I need to convert the data t whole numbers? Thank you in advance. : -- calimar ----------------------------------------------------------------------- calimari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2453 View this thread: http://www.excelforum.com/showthread.php?threadid=39022 Assuming you don't have ...

Database query, spreadsheet size
I have a spreadsheet with several database queries from SQL Server. As queries are refreshed, spreadsheet size continues to grow. Even when I wipe out the queries, the file stays around 300K. Does anyone know where this extra data is sitting and how can I clear it out. Hi 300K is not that big but have a look at: http://www.contextures.com/xlfaqApp.html#Unused >-----Original Message----- >I have a spreadsheet with several database queries from >SQL Server. As queries are refreshed, spreadsheet size >continues to grow. Even when I wipe out the queries, the >file stay...

message is larger than the current system limit
I am new to Exchage Server administration. I have Exchange 6.5 SP2. I can't get an email out with a 12M attachment. We have to send such a large file about once a year. I have read, and, I believe, followed the instructions of Article 322679. My user account send limit is: 200000 (KB). Golbal Message Delivery Property is "No Limit." I set Default Virtual Server Send Max to 200000, Session Max 300000. Set Connector to send Max 200000. I ran gpupdate /force on server and client. After that, I turned off cached mode on the client; restarted Outlook 2003; Turned Cached Mode b...

Keep quoted numeric characters as text.
Is there any way I can keep Excell from converting double quoted strings of numeric characters to a number when opening a .csv file? I have a column where sometimes the text being imported is something like "001". I need to load it as text '001' instead of as a number 1. If I change the column to text after opening the file, the leading zeros are still missing. In MacXL you can open it directly with Text Import wizard. With WinXL you need to change the extension (say, to .txt) first. Then set the column to Text in the third pane of the wizard. In article <043b...

Deleting Leading Characters
I just exported a file from monarch to excel. Unfortunately, some of th columns have a leading (') in each of the cells. I tried using TRIM, bu it did not work. How can I delete the leading character (')? I have 50 rows with multiple columns : -- inthewa ----------------------------------------------------------------------- intheway's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=672 View this thread: http://www.excelforum.com/showthread.php?threadid=26923 try replacing ' with "" -- duan ----------------------------------------------...

Ldap Query
I want to make a query where i can see the members of an administrative group or Distribution list. Is there someone who has such a query? administrative group would be based on something similar to (legacyexhangedn="/o=Microsoft/ou=First Administrative Group/cn=Recipients") dl would be based on (memberOf="DN of Group") This works fine as long as the group isn't the users primary group -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this...

How to know that character type in one cell of xls is more that s.
Q : How to know that character type in one cell of xls is more that s... E.g How i will come to know that i typed more than specified charater, when i am typing in that particular cell Your character to test is in A1 In another cell enter =CODE(A1)>CODE("s"). You will get TRUE or FALSE Or use =IF(CODE(A1)>CODE("s"),"Bigger","Not bigger") -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Dharamendra" <Dharamendra @discussions.microsoft.com> wrote in message news:C2427962-5283-43FD-B9B0-5B367EA2470A@microso...

Union Query
A union query I developed has suddenly stopped working. The syntax is: SELECT * FROM [myQuery - Part 1] UNION SELECT * FROM [myQuery - Part 2]; Pulling up each query individually works fine. When i run the above union query I receive "Invalid use of Null". The number and order of the columns in each query are identical. Access 2003 here. Any ideas what could be the problem? I suspect it is the VBA code that you are using around the query, not the query, that stopped working. To confirm, does your query is executable from the query editor? If it does not, you also confirm t...

Use Stored Procedure developed in C# in a SQL Query
I created a Stored Procedure by selecting a SQL Server Project in Visual Studio 2008 using C#. It receives a SqlString as a parameter and return how many times a specific character was found. I deployed the procedure to a SQL Server database and can see it listed under the database. One caveat is that it has a small lock icon on it. I have a select query where I want to use the stored procedure. I am thinking this is doable in a select query but can't seem to get it to work. What I want to do is to have a select statement listing some fields and one of the fields being th...

webdings characters and word
hey, im trying to do a mail merge to word and one of the columns on m spreadsheet ive formatted with webdings and a "c" character cos i nee to have a box on the word page, but when i do the mail merge it jus comes up as a "c". if I format the text in word as webdings it gives m a half block kind of thing. any ideas? thank -- Message posted from http://www.ExcelForum.com Matt In the cell is the letter c. That's it!! It's the Wingdings font that makes it look like the graphic. You'd have to format the mailmerge font to Wingdings for that letter, to get the sa...

Insert Database doesn't "see" half my queries?
I am trying to insert a database” from Access 03 into Word 03 using the database toolbar in word. When I click “Insert Database” in word, and then browse for my query, the wizard doesn’t “see” any of the queries in which I changed a field name (ex. Dept: Department). Does anyone know a workaround for this? Right now, it’s only showing about half my queries which is a little odd. Is there a different way to insert my database? Thank you! If you check Word Tools->Options->General->"Confirm conversion at open" and go through the whole insertion process aga...

Select subform's query criteria from form's unbound cbo
I want users to be able to select a name from a form's unbound combo box and that selection to automatically become the criteria for the subform's underlying query, without the parameter box for the query popping up. How do I do this? I'm new to VBA and not an Access 2003 whiz either. TIA for your help! WorkerBee wrote: > I want users to be able to select a name from a form's unbound combo > box and that selection to automatically become the criteria for the > subform's underlying query, without the parameter box for the query > popping up. How do I do t...

shortcut condenced character space
i want to condense character space with short key, You can assign a shortcut key to a specific amount of condensation. In the Tools | Customize | Keyboard dialog, select All Commands as the category. In the Commands list, scroll down to Condensed: and type in the desired amount or select it from the dropdown. Then press the desired keyboard shortcut and click Assign. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "imran" <imran@discussions.microsoft.com> wrote in message news:8AF23718-2357-454C-BD2...

Relationship error in a query
Hello Everybody, I have four tables in a databse. Each database has at least one column containing prices of goods & services. However, these columns are not related in anyway (some other colums are). I am trying to summerize these price information in one query where I am trying to list the sum of prices from every table. When I try to do so I am getting an error part of which says: "One or more of the tables isn't related to the others. Click OK to edit system relationships." At least according to the error message (and this would have been my question anyway),...

Query table using criteria from an Excel document
Hello, I have a Access table of names and addresses. There is a postcode (zipcode) text field. I also have an excel spreadsheet with a single column of postcodes; no duplicates. I need to get query to return each name and address in the table which has a postcode listed in the spreadsheet. Typing each postcode manually into the query seems clumsy. Any ideas? Add a link to the Excel data. Create a new query joining the linked Excel data and your Access table on the postcode field. Those that match will result. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/A...

macro to change column to keep only last 5 characters
I'm not sure how I can use left or right or trim on this, but any suggestions? Formula method. Assuming data in column A, in B1 enter =RIGHT(A1,5) and double-click fill handle to copy down. Macro method. You choose the activecell. Sub trimmit() Dim rng As Range Set rng = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)) For Each cell In rng cell.Value = Right(cell.Value, 5) Next End Sub Gord Dibben MS Excel MVP On Thu, 6 Dec 2007 13:33:51 -0800 (PST), S Himmelrich <himmelrich@gmail.com> wrote: >I'm not sure how I can use left or right ...

::: Outlook 2003 - TaskPad
Hello, Could anyone tell me if the TaskPad has a limited amount of task that can be enter? If no perfect! If yes what is the limit (in number of tasks)? In other words can I enter as many tasks as I wont or not? Many thanks for your help! Have a good day, Jacques infojacques@gmail.com <infojacques@gmail.com> wrote: > Could anyone tell me if the TaskPad has a limited amount of task that > can be enter? > If no perfect! > If yes what is the limit (in number of tasks)? > In other words can I enter as many tasks as I wont or not? The Tasks folder itself, depending on you...

query criteria 12-29-07
I have a criteria test in the [DR].[Received Date] field of a query. The criteria is based on data which is entered into a form. >=(IIf(IsNull([Forms]![CSR dg]![txtEarliestReceivedDate]),[DR].[Received Date],[Forms]![CSR dg]![txtEarliestReceivedDate])) The true side of the IIF allows for all received dates if no data is entered into the form. I would like to instead not do a criteria test at all if there is no date entered into the form. I've tried things like "" or NULL in the criteria, but to no avail. The reason for wanting to do this, is that I have two tables linke...

Still stuck: rerunning a query to change criteria
My form lets one set criteria for a query, from which some textboxes display several Dcounts of the selected clients (grouped and counted by age and gender). Now I need this query to be rerun once different criteria are set. But I believe that as long as the form is open, the query wil be run ones and then never again! I could of course have people close and open the form again and again, but that is cumbersome. Me next try would be to have the criteria be set i the form and then as next step run and open the query, including the data to be displayed in a subform. But is ...

How can I limit the number of emails sent in a given time?
I work at a small business and I need to send an email to a list of several hundred custoers on a regular basis. However, my ISP limits the number of emails sent out to 20 per every five minutes. Is there a way to configure Outlook 2003 to send them in bursts or anything? I plan on using the mail merge through Word 2003, if that makes a difference. Any suggestions or help would be greatly appreciated! Mail Merge will send individual messages, but may exceet the 20 per five minute period. I would contact the ISP and you could possibly convert to a "business" account, probabl...

fail to create IXSSO.Query object
I'm moving classic asp sites from Windows 2000 / IIS 5 to Windows 2008 R2 / IIS 7. We have the "Indexing Service" started. Catalogs were created OK. However, I am unable to create an ixsso.Query object. Get the following error message Microsoft VBScript runtime error '800a01ad' ActiveX component can't create object /HC/ECO/Announcements/Headlines/SearchResults.asp, line 39 ASP code : <% Dim sSearchString Dim oQuery Dim results() ...

missing characters in printed copy
i created a mail merge document. just plain text with one single image(signature on letter). after completing/printing the job. we just realized that some characters are missing within the body of the letter. i cant figure out what seems to be or root of the problem. i tried to save the same exact document to previous version..(97-2003) and then tried to print a couple of sheets....it looks perfect. can somebody gave me info what may have caused that problem? here's some additional info: o.s. xp word 2007 printer : canon ir110 using pcl driver note: used the same fl...

Priv.edb query utility
Recently I ran across a utility on technet somewhere that you can run against the priv.edb for exchange 2003, and it can spitback how much of a percentage that each users mailbox is compromised of specific attachment formats, by extension name, not by querying the attachments mime information. It spat back graphical information in pie chart format, to show for example: user 1's mailbox is 15% compromised of mpegs, and avis etc It can also spit back the total % of the overall priv.edb is comprised of 15% specific formats(in our case, movie file formats, mpeg, avi etc). Does anyone...

Supporting double-byte characters in Exchange 2003 SP2
How do I confirm that our new Exchange 2003 SP2 server will support all double-byte character languages (i.e. Japanese, Chinese, etc.)? We are going to be doing a lot of communicating with East Asian companies and I want to be sure that our Exchange 2003 SP2 Server won't "break" emails that arrive using non-Western character sets in them. Thanks. ...

random incomplete query results
Hi to all, on a form I'm filling a "box" (I don't know the english name, is like a combo without the input selection row) from a table on the same database. To do that, I'm using the property "RowSource", also to change the ordering field. The problem is that, when I'm using the database on a PC with Windows 2000 and Office 2000, "sometime" (exactly! not all the times!) the list on the box is covering only a fraction of the entire table! After some trials I get the full list... The same database, on my PC (XP and Office 2003), is working alw...