Dear Experts,
I was was wondering if there was some way to specify output properties of a make table query. Specifically, I am haivng a problem with numbers being specified as text. while I realize I can go in and change the table properties after the table has been created, I create and replace this table quite often so that would become a laborious prcess. Is there some way I can make the query specify that for me. I am using access 2003 and the VBA script of the make talbe query is as follows: -
SELECT Accounts.AccountNumber, Accounts.AccountName, Section1Budget.budgetedAmount, NZ([Section 1 Reappropriations]![budgetedAmount],0) AS Reappropriations, NZ(NZ([Section1Budget]![budgetedAmount])+NZ([Section 1 Reappropriations]![budgetedAmount]),0) AS [current appropriations], NZ([section1directPayVouchers]![SumOfAmount])+ NZ([Section1SPO]![SumOfAmount])+ NZ([Section1Requsitions]![SumOfAmount]) AS Encumbrances, NZ([current appropriations]-[Encumbrances],0) AS [budget afterencumbrances], NZ([appaidsection1]![SumOfAPPaidAmount],0) AS APPaid, NZ([current appropriations],0)-NZ([APPaid],0) AS [Expendable Funds] INTO section1report
-
FROM Section1Budget RIGHT JOIN (Section1Requsitions RIGHT JOIN (section1directPayVouchers RIGHT JOIN ([Section 1 Reappropriations] RIGHT JOIN (appaidsection1 RIGHT JOIN (Accounts LEFT JOIN Section1SPO ON Accounts.AccountNumber = Section1SPO.AccountNumber) ON appaidsection1.AccountNumber1 = Accounts.AccountNumber) ON [Section 1 Reappropriations].[Account Number] = Accounts.AccountNumber) ON section1directPayVouchers.Account = Accounts.AccountNumber) ON Section1Requsitions.Account = Accounts.AccountNumber) ON Section1Budget.[Account Number] = Accounts.AccountNumber;
-
any help you can give me would be greatly appreciated.
-Andy
5  1802  
Dear Experts,
I was was wondering if there was some way to specify output properties of a make table query. Specifically, I am haivng a problem with numbers being specified as text. while I realize I can go in and change the table properties after the table has been created, I create and replace this table quite often so that would become a laborious prcess. Is there some way I can make the query specify that for me. I am using access 2003 and the VBA script of the make talbe query is as follows:
- SELECT Accounts.AccountNumber, Accounts.AccountName, Section1Budget.budgetedAmount, NZ([Section 1 Reappropriations]![budgetedAmount],0) AS Reappropriations, NZ(NZ([Section1Budget]![budgetedAmount])+NZ([Section 1 Reappropriations]![budgetedAmount]),0) AS [current appropriations], NZ([section1directPayVouchers]![SumOfAmount])+ NZ([Section1SPO]![SumOfAmount])+ NZ([Section1Requsitions]![SumOfAmount]) AS Encumbrances, NZ([current appropriations]-[Encumbrances],0) AS [budget afterencumbrances], NZ([appaidsection1]![SumOfAPPaidAmount],0) AS APPaid, NZ([current appropriations],0)-NZ([APPaid],0) AS [Expendable Funds] INTO section1report
-
FROM Section1Budget RIGHT JOIN (Section1Requsitions RIGHT JOIN (section1directPayVouchers RIGHT JOIN ([Section 1 Reappropriations] RIGHT JOIN (appaidsection1 RIGHT JOIN (Accounts LEFT JOIN Section1SPO ON Accounts.AccountNumber = Section1SPO.AccountNumber) ON appaidsection1.AccountNumber1 = Accounts.AccountNumber) ON [Section 1 Reappropriations].[Account Number] = Accounts.AccountNumber) ON section1directPayVouchers.Account = Accounts.AccountNumber) ON Section1Requsitions.Account = Accounts.AccountNumber) ON Section1Budget.[Account Number] = Accounts.AccountNumber;
any help you can give me would be greatly appreciated.
-Andy
Field names and data types are inherited from the source table in a make table query...
Are you saying that this isn't happening, and that instead some number data type fields are being transferred across into text fields? Which specific fields in the sql above is this happening with?
Regards,
Scott
Field names and data types are inherited from the source table in a make table query...
Are you saying that this isn't happening, and that instead some number data type fields are being transferred across into text fields? Which specific fields in the sql above is this happening with?
Regards,
Scott
It is happening to the appropriations field, reappropriaions field, budget after encumbrances, and APPaid
It is happening to the appropriations field, reappropriaions field, budget after encumbrances, and APPaid
and yeah... all the tables it works off of are all correct and represent the numbrs as numbers..... but for some reason when it translates into this query it turns the numbers into text fields
Strange... I'm not sure why it's doing it this way, when it should be inheriting the field attributes from your parent table.
There's no way that I know of to make an SQL make-table query reset the field attributes. To do this programmaticall y you'll need to explore the DAO tabledefs/indexes/fields/attributes collections.
Regards,
Scott
 MMcCarthy 14,534
Recognized Expert Moderator MVP
Firstly you haven't specified the value if Null on many of the nz() functions. I think the default is "". This may be your problem. Try the following: -
SELECT Accounts.AccountNumber, Accounts.AccountName, Section1Budget.budgetedAmount, NZ([Section 1 Reappropriations]![budgetedAmount],0) AS Reappropriations, NZ(NZ([Section1Budget]![budgetedAmount],0)+NZ([Section 1 Reappropriations]![budgetedAmount],0) AS [current appropriations], NZ([section1directPayVouchers]![SumOfAmount],0)+ NZ([Section1SPO]![SumOfAmount],0)+ NZ([Section1Requsitions]![SumOfAmount],0) AS Encumbrances, NZ([current appropriations]-[Encumbrances]),0) AS [budget afterencumbrances], NZ([appaidsection1]![SumOfAPPaidAmount],0) AS APPaid, NZ([current appropriations],0)-NZ([APPaid],0) AS [Expendable Funds] INTO section1report
-
FROM Section1Budget RIGHT JOIN (Section1Requsitions RIGHT JOIN (section1directPayVouchers RIGHT JOIN ([Section 1 Reappropriations] RIGHT JOIN (appaidsection1 RIGHT JOIN (Accounts LEFT JOIN Section1SPO ON Accounts.AccountNumber = Section1SPO.AccountNumber) ON appaidsection1.AccountNumber1 = Accounts.AccountNumber) ON [Section 1 Reappropriations].[Account Number] = Accounts.AccountNumber) ON section1directPayVouchers.Account = Accounts.AccountNumber) ON Section1Requsitions.Account = Accounts.AccountNumber) ON Section1Budget.[Account Number] = Accounts.AccountNumber;
-
If that doesn't work there is a CCur() function you can use around the calculations which should force the appropriate output.
CCur(NZ([Section 1 Reappropriation s]![budgetedAmount],0)) AS Reappropriation s
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Bill |
last post by:
I just discovered that all my records appear twice inside my table, in
other words, they repeat on the row below. How can I delete all of the
duplicates? I'm sure there must be a tidy line of sql to do that.
Thanks,
Bill
|
by: Kathy Krizl |
last post by:
I'm probably doing something stupid, but I have a make table query.
One of the tables I reference has some check box fields in it. Their
Data Type is Yes/No, their field property format is Yes/No, they have
no default value, and their Display Control is Check Box. When I run
my make table query, the new fields have a Yes/No Data Type, but the
field property format comes up blank, and the Display Control comes up
as Text Box. How come...
|
by: LesM |
last post by:
This is a change of behaviour between Access 2000 SP3 and Access 2002
SP3.
I have Progress table that is linked via ODBC into Access using OpenLink
Lite for Progress 9.0b. For over a year, using Access 2000 under Win98,
I have been running a Make Table or Append query against this ODBC
table and producing an extracted Access table. Any fields that are NULL
on the source ODBC table show as NULL on the output table. The query
also has an...
|
by: Bob Alston |
last post by:
Anyone know a way to make all access to a linked table, in another
Access MDB, read only?
I really don't want all the hassle of implementing full access security.
I can't do this at the server file system because in some front ends the
user needs update access.
I want to give users access to the data for reporting with their own
queries and reports. I just don't want to take a chance on them
|
by: Daron |
last post by:
How do I sort a table, from a form. I can't use SQL!
WE have an Access application that is created by an outside vendor. The
one form states: "Please make sure all records in table are sorted by
date..."
The application is not locked or hidden in any way, but is fairly
involved. I would like to be able to simply add a button to this form
to sort the table.
| | |
by: ken |
last post by:
Hi,
I use this command to run a make table query without opening it...
CurrentDb.Execute "make table query name"
Access tells me that it can't execute a select query...? Its a make
table query not a select? Or are those two the same.
Bottom line is that the DoCmd.OpenQuery alowes the user to see the
query and I don't want that...
Anything else I could use other then Currentdb.execute?
|
by: otac0n |
last post by:
How can I select the maximum value from a field, but make sure that the
value i want stays greater than a cetrain value?
I currently have this:
SELECT
A.rating,
B.rating,
1/(1+10^((A.rating-B.rating)/400)) AS Expected,
Round( -20*( .+. ) / (
|
by: narpet |
last post by:
Hello all.
I have a table in Access that has a check box as one of the fields. I want to write a query or code that will check or uncheck that box based on conditions met by other fields in the table. Here are the specifics as well as I can explain it:
I have the following table fields:
EmployerID
Group
Payroll
Yes/No
|
by: Robertf987 |
last post by:
Hi, I'm a bit stuck with an access database. I'm using access 2000 if that's any help.
Right, it's 3:40am right now and I'm rather tired, but I *hope* this makes sense.
I have a table which contains the main data of the database, records of information of grants. For each financial year, the old data needs taking out of the table. Basically I have two options: delete the old data from the year before, or back it up in a new table. I'd rather...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
