modifying field values in an Access 2003 query

Someone please help.  Is there a SQL or Visual Basic statement that can be 
used to set/change the values in a field in an Access 2003 query to a default 
value?  
0
Utf
11/26/2007 8:34:09 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1131 Views

Similar Articles

[PageSpeed] 36

I'm more familiar with the concept of a table's field (or a control on a 
form) having a "default" value.  How is it that you expect a query's field 
to have a default value?  What are the circumstances?

If, for example, you want to display something like "n/a" when an underlying 
field in a table contains a Null, you could use something like:

    YourQueryField: 
IIF(IsNull([YourUnderlyingField]),"n/a",[YourUnderlyingField])

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jan07" <Jan07@discussions.microsoft.com> wrote in message 
news:3864D750-D34F-4C5B-B014-BFE4C994CAE7@microsoft.com...
> Someone please help.  Is there a SQL or Visual Basic statement that can be
> used to set/change the values in a field in an Access 2003 query to a 
> default
> value? 


0
Jeff
11/26/2007 8:42:44 PM
Thank you…the circumstances are a bit confusing, but I’ll attempt to explain. 
 I need to provide a monthly count of different transaction types which are 
stored in a single table in the database.  The various transaction types are 
identified by a [Tr_type] field containing single letter or letter 
combination values.  The problem is that some records are added to the 
database as one transaction type even though they are really two different 
types of transactions, which need to be grouped and counted separately. These 
particular records can be distinguished by their transaction numbers (a 
trans_num field).  What I am trying to do in the query is change the 
[Tr_type] of those records if the value in the [trans_num] field follows a 
specified sequence, so that they can be grouped and tallied separately in a 
report.  I cannot utilize an update query to change the transaction type in 
the underlying table because I need to create a report that the user can run 
without having to do an update query first. I’m wondering if there is some 
type of “If” statement or function that can be used in the query to 
accomplish this.  I can only use the transaction number to distinguish this 
particular group of records in the database…for all other records I have to 
use the transaction type.  

Thanks for reading...Jan07

"Jeff Boyce" wrote:

> I'm more familiar with the concept of a table's field (or a control on a 
> form) having a "default" value.  How is it that you expect a query's field 
> to have a default value?  What are the circumstances?
> 
> If, for example, you want to display something like "n/a" when an underlying 
> field in a table contains a Null, you could use something like:
> 
>     YourQueryField: 
> IIF(IsNull([YourUnderlyingField]),"n/a",[YourUnderlyingField])
> 
> Regards
> 
> Jeff Boyce
> Microsoft Office/Access MVP
> 
> "Jan07" <Jan07@discussions.microsoft.com> wrote in message 
> news:3864D750-D34F-4C5B-B014-BFE4C994CAE7@microsoft.com...
> > Someone please help.  Is there a SQL or Visual Basic statement that can be
> > used to set/change the values in a field in an Access 2003 query to a 
> > default
> > value? 
> 
> 
> 
0
Utf
11/26/2007 10:21:00 PM
I may not understand enough yet, but I suspect the IIF() statement/function 
should be able to do what you are describing.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jan07" <Jan07@discussions.microsoft.com> wrote in message 
news:72094ADB-7ED2-4F13-A042-C0B344021334@microsoft.com...
> Thank you.the circumstances are a bit confusing, but I'll attempt to 
> explain.
> I need to provide a monthly count of different transaction types which are
> stored in a single table in the database.  The various transaction types 
> are
> identified by a [Tr_type] field containing single letter or letter
> combination values.  The problem is that some records are added to the
> database as one transaction type even though they are really two different
> types of transactions, which need to be grouped and counted separately. 
> These
> particular records can be distinguished by their transaction numbers (a
> trans_num field).  What I am trying to do in the query is change the
> [Tr_type] of those records if the value in the [trans_num] field follows a
> specified sequence, so that they can be grouped and tallied separately in 
> a
> report.  I cannot utilize an update query to change the transaction type 
> in
> the underlying table because I need to create a report that the user can 
> run
> without having to do an update query first. I'm wondering if there is some
> type of "If" statement or function that can be used in the query to
> accomplish this.  I can only use the transaction number to distinguish 
> this
> particular group of records in the database.for all other records I have 
> to
> use the transaction type.
>
> Thanks for reading...Jan07
>
> "Jeff Boyce" wrote:
>
>> I'm more familiar with the concept of a table's field (or a control on a
>> form) having a "default" value.  How is it that you expect a query's 
>> field
>> to have a default value?  What are the circumstances?
>>
>> If, for example, you want to display something like "n/a" when an 
>> underlying
>> field in a table contains a Null, you could use something like:
>>
>>     YourQueryField:
>> IIF(IsNull([YourUnderlyingField]),"n/a",[YourUnderlyingField])
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "Jan07" <Jan07@discussions.microsoft.com> wrote in message
>> news:3864D750-D34F-4C5B-B014-BFE4C994CAE7@microsoft.com...
>> > Someone please help.  Is there a SQL or Visual Basic statement that can 
>> > be
>> > used to set/change the values in a field in an Access 2003 query to a
>> > default
>> > value?
>>
>>
>> 


0
Jeff
11/26/2007 11:15:17 PM
Reply:

Similar Artilces:

Union Query
Hi, I need some help with a query. I believe that a Union Query is appropriate, but I am unsure. I have two make table queries. One makes a table called QTY_RECEIVED that has the following field names: Part#, Fiscal_Week, Fiscal_Year, and Receipts. The 2nd table is called SUPPLY_REQUIREMENT which has the following fields: Part#, Fiscal_Week, Fiscal_Year, and Demand. I want to see the history of Demand and Receipts for all the parts that I have entered. Problem is that some weeks there is no demand and some weeks there is no Receipts. If I link the two tables by Part# and Fiscal Week t...

Modifier Error
Whenever we try to go to modifier it gets the following error. The Modifier is currently unavailable because another user is editing resources in the Forms Dictionary. The Server has already been rebooted but we are still coming up with the same error. Thanks, Hi make sure that you are not sharing the Forms Dictionary with another users. Thanks BS "Jess M." <JessM@discussions.microsoft.com> wrote in message news:C56C6878-9410-4CF5-95DE-DCA0AC50EA55@microsoft.com... > Whenever we try to go to modifier it gets the following error. > > The Modifier is current...

Outlook .pst cannot be accessed
Starting today, I cannot download email (Office Outlook 2007/VISTA). I get the message "Cannot start reminder service. Cannot show reminder. The message interface has returned an unknown error. If this persists restart Outlook. Cannot open item. Item may be damaged." I have shut down and restarted my computer four times with no change. The Unread Items folder is gone along with my contacts in the address book, and the calendar. And, I cannot move messages to other folders. There is also a message saying "Receiving error 0x80040600." Does anyone know what the p...

Excel Front End with Access Back End #2
Yes, I would have one central Access Database that sits on a server wit copies of the Excel front end on the users laptops. I just need to kno how to create the interface between them. Regards, Ripa -- Ripa ----------------------------------------------------------------------- Ripan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=471 View this thread: http://www.excelforum.com/showthread.php?threadid=26704 hi, in access you can link to excell files like you can link to another database. I do it all the time. and the linked excel files work just link acc...

getting column and row names if a cell has a value
Hello, I am trying to pull data out of a large Excel spreadsheet to get a slimmed down version.. I have a large spreadsheet (100+ columns by 500+ rows, and 12 worksheets) that I'm trying to make sense of in order to import into another program. Each Column has a four-digit code for a "name", each row has a five- digit code for its "name." The majority of cells have a value of '0'. I am trying to get excel to look at the range, and if there is a value that is not zero in a cell, give me the column name in one column, the row name in the next column then the v...

Using the highest count for the field name in a query
I have a table that list a count of peoples visits along with the State City and 5 digit Zip Code. I would like to query this data so I can get a total(Sum) of peoples visits by their 5 digit zip code. This is difficult because some Zip Codes can have more than 1 town associated with that 5 digit zip code. I would like the total for all trips in that zip code but have only the city or town with the most visits listed in the city field. For instance I would like the table below City State Trips 5 Digit Zip Anytown OH 1 18555 Anytown OH 2 18555 Farmtown...

Selecting a Value from a Range of Values
I want to select a value from a range of 20 Values depending upon the relationship between two cells outside that range. Lets say the Range of Values I want to select from is held in A1 to A20 I then Have a Range of Different Values in held in C1 to C24 - C1 = 1, C2 =2 ..... C24 = 24 In E1 I have a Value that could be anything from 1 to 24 If C1 = E1, I want to put the Value in A1 into D1, A2 into D2, A3 into D3 etc If C2 = E1, I want to put 0 into D1,the Value in A1 into D2, A2 into D3, A3 into D4 etc Can it be done? Simplest way would be with one formula in D1, And another in D2, wh...

Outlook 2003 will not close!
Outlook will not close! I'm not using a exchange server. I have to close outlook 2003 with the task manager. This happened after upgrading office XP to 2003. Runing with winfax Pro 10.01 WinFax 10 is not compatible with Outlook 2003. Might want to check the Symantec support site and see if they have documented steps on how to install WinFax 10 w/out integrating into Outlook. -- Neo [MVP Outlook] Due to the Swen virus, all e-mails sent to this account will be deleted w/out reading. "sendmeabuck" <sendmeabuck@hotmail.com> wrote in message news:095201c39fb3$046f20e0$a6012...

criteria field to auto filter
ok that seems to work but the problem now is that the screen is blank, if i select the filter the date in the field is correct with the right criteria but i have to select OK for it to work, how can i get it to select OK automatically so that will display all the transaction dates... thanks for this >>>> The < operator should be enclosed in quotation marks, and followed by an ampersand: Selection.AutoFilter field:=4, Criteria1:="<" & lessdays dhbyrne wrote: > Hi, > I am trying to write a macro that will use the autofilter function in Excel. I ha...

EXE change in data modified
Hi Guys Have a question regarding GP. What can cause a change in the "modified date" to the dynamics.exe file? In theory, something would need to change the file. My best guess would be a service pack would change the date. -- www.fmtconsultants.com "cruesta@gmail.com" wrote: > Hi Guys > > Have a question regarding GP. What can cause a change in the "modified > date" to the dynamics.exe file? > > For GP 9.0 SP 1 the Dynamics.exe modified date is 5/16/2006 which looks to be the date the Service Pack was created (as opposed to the da...

Erroneous 'Enter Parameter Value' dialog after converting to Acc 2
After converting from access 97 to access 2000 when I try to open some of my reports from code I get an 'Enter Parameter Value' dialog appearing. There is no field name listed in the dialog and when I press the OK button the reports appears just as in Access 97. Is there any way to suppress this dialog from appearing? Answered in m.p.a.reports ...

changing forumla results to values on the fly
Is there a way to get a cell's forumla to convert to its resulting value on the fly (rather than having to go back in and cut>paste special>values)? Alternatively, is there a way to use a second column that will display the values returned from the formulas in the first column as hard-codes values? Thanks in advance. Click in the cell, then: <F2> <F9> -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------...

VB.Net: how to carry SessionID(its value ) from 1 function to othe
If I get SessionID in 1 function how do I carry that SessionID(value of this SessionID) to another function or another form within the same project Winforms or webforms? In Winforms, u can make the SessionID a public or internal property of the form and in WebForms use the Session object to hold this value. "XML newbie: Urgent pls help!" <XMLnewbieUrgentplshelp@discussions.microsoft.com> wrote in message news:4350B554-0144-474C-893C-2269C59F75C6@microsoft.com... > If I get SessionID in 1 function how do I carry that SessionID(value of > this > SessionID) to ano...

Problem to modify the size of PlotArea
Hello, I am trying to modify a chart so that it prints in a 20cm x 17,5cm frame. I do understand that this is governed by the plotarea.insideheight and plotarea.insidewidth properties. I also do understant that these properties are read-only. So far my code is the following: With ActiveChart .SizeWithWindow = False .PageSetup.ChartSize = xlScreenSize .PlotArea.Width = 567 .PlotArea.Height = 496 Do While .PlotArea.InsideWidth < 567 .PlotArea.Width = .Pl...

How Do I change the User name field
Running WinXp SP2 with Office 2000 I have 100+ PCs that I need to change the Username field so that when the user gets 'File is locked' message, I know who originally opened the file. I know this can be manually done by going Tools - Options - General. But with 100+ PCs I would prefer to able to this either by Group policy or a script?? Does anyone have any ideas. ...

modified timeline graph
I have a table of dates and events (and "phase"), and I want to make a timeline graph: 1-d horizontal line, no (visible) y-axis. I want the distance between the events to be spaced based on the date, and the event names to be shown at each point. The dates should be shown too, either for each event or just based on auto-scaling of the x-axis (e.g. major unit = 3 months). Here's the hard part. The events happen in different "phases", and I want to show the phases graphically somehow. I was thinking this component of the graph would be a bar chart, and I could someho...

cant get outlook 2003 to work in Windows 7
installed office 2003 standard (sp3) in windows 7 and get a trouble message when launching. A popup box called configuring outlook comes up with the task "migrating account settings" highlited. This is followed by the trouble message box called microsoft office outlook stating "unable to open your default email folders. Outlook would not start because a data file to send and receive could not be found". I went back into the windows control panel>user accounts>mail and verified a pop email exists but there no entries under the data tab. I selected add (...

Can't trim white space from large varchar field
Hi, I am reading two very large varchar fields (2600) from the sql server 2005 database of our ERP. This reading is being done by a cursor. In the cursor select statement I rtrim and ltrim the field. No matter what I do the length of the field is always 2600. I can see that there is some sort of white space to the right of the text. Whatever that white space is, it can't be trimmed by ltrim or rtrim. I tried running the sql from the cursor as a query and had the same problem. Any help would be appreciate it. fig000 If you do a RTrim of a varchar column, the...

Exchange 2003 mount public db
Hi, I have Exchange 2003 SP2 on SBS Server. On of my customer losed old mail on a public folder and I need to restore emails. I have the database file that contains the emails but i wouldn't stop exchange mount the old db (DBOLD) create e pst of email, dismount the db and mount the official db. It's too dangerous! Then I reinstall the same machine on a server with the same domain, organization, Admnistrative group of Exchange. Dismount the original db and I try to mount the db of the other server (DBOLD). I receive an error like this: The information store could not be loaded bec...

Excel To Access: Help with getting a range data from excel to acce
Hi All, How can I transfer a Bock of data to Access from excel by clicking a Submit" button? eg: There is a "Submit" button on the excel sheet that exports all of the student data at once in the access database.. I want to insert all this data in access at the same time. Here is what My excel table looks like: Student_ID Subjects Grades 123456 Eng A 123456 Hist B 123456 Math B+ 123456 Bio B- So, once we click "Submit" I want the data displayed above to go to access, I...

memo/payee fields
Even though my wife's employer's name is listed in the 'memo' field for a downloaded transaction, money insists on auto-filling the 'from' field with my employer. anyone know how to change this stuff? happens with grocery stores too - even though the transaction is downloaded from kroger, the 'from' field will always say 'meijer', for example. i suspect it's the same problem. Sound like your bank is sending malformed data. Typical. Try calling them and telling them to get with the program. "russ" <rbowman88@cinci.rr.com> w...

Access Issues
I am very new to CRM, just got thrown in at the deep end due to a new job. For some reason i can't access web CRM on my PC or the server. As soon as i try to connect i get, it does not even as me for a username or password. Authentication Error Microsoft CRM could not log you on to the system. Make sure your user record is enabled and that you have been assigned at least one security role. For more information, contact your system administrator. Log onto random another PCs and it works fine!! Try this: Internet Explorer - Tools - Internet options - Local intranet - User authenticati...

Outlook 2003 is slow to switch from Mail to Calendar, why?
it takes about 15 seconds to switch from mail view to calendar - can someone offer a suggestion? My outlook.pst is only 250 MB ...

Problem importing leads/contacts with embedded comma in fields
When importing .csv file, if a text field that is delimited with double quotes contains a comma the import tool still see the comman and separated the field into two or more fields, which then shifts all the following fields into the wrong position. Am I missing a fix. I am running CRM 1.2 with the last rollup. Was there an earilier patch to fix this. Thanks - Bob There is no fix. You have to ensure your CSV data is clean. -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Robert Morris" <RobertMorris@discussions.microsoft.com> wrote in message news:7EBD...

Matching a value in two worksheets, and then copying a cell from one to other
Hi there, Noob alert :) I have a workbook containing several worksheets. I will be adding another worksheet that will, in effect, be a master for all of the other worksheets. In the source worksheets (existing) there will be several employee id's (unique) per sheet (sheets are different departments), and a seperate column housing that employees pay rate. Any employee may work for one or more departments, so there may be duplicate id's when considering all of the worksheets, but in each, the id will be unique. What I would like to do, is have the master worksheet with an individual u...