I have two tables that are linked by a common field called 'reference'. i am running a query and want the query to post a text of "EXCHANGE OV CHANGE" against each record where a field called "ORIG Value", present in both tables, differs by reference number
Expr2: IIf([Bank Gtes]![ORIG_VALUE]<>[BG Last AP]![ORIG_VALUE],"EXCHANGE OV CHANGE","NOT EX")
The BG Last AP table is linked from Excel and the Bank Gtes table is within the Access database.
The problem I have is that the linked table displays it's ORIG_VALUE data with two decimal places - yet when I click in the cell some of the numbers only have one decimal place ie 4569760.1 - the value in the other table for the same reference number is 4569760.10. So these are the same number. Yet when I run the query records like this one have the "EXCHANGE OV CHANGE" text against them. The query thinks they are different due to the additional 0 at the end of the record. As the table is linked how can I change the format so that these records have an additional 0 to give the value 2 decimal places.
I've tried formatting the cells in the linked table but with no joy
Any help would be greatly appreciated!!
thanks
4  5196  ChipR 1,287
Recognized Expert Top Contributor
You may need to add a field or two to your query to make sure the values are properly formatted, since you are likely comparing text fields.
GtesValue: FormatNumber([Bank Gtes].orig_value, 2)
BGAPValue: FormatNumber([BG Last AP].orig_value, 2)
Then,
Expr2: IIf(GtesValue<> BGAPValue...
I like the logic! - However access gives me an Undefined function "Format number" in the expression
any ideas?
thanks
ChipR 1,287
Recognized Expert Top Contributor
FormatNumber should not have a space. If you are using it that way and for some reason it is not available in the version of Access that you have, you could use Format([Bank Gtes].orig_value, "Currency")
worked perfectly thanks - you are a star!
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Bernie |
last post by:
Greetings,
I have 3 servers all running SQL Server 2000 - 8.00.818. Lets call
them parent, child1, and child 2.
On parent, I create a view called item as follows:
CREATE view Item as
select * from child1.dbchild1.dbo.Item union all
select * from child2.DBChild2.dbo.Item
|
by: Ariel |
last post by:
Hi, I have a problem, maybe someone can help me.
I'm traing to create a view with a Linked Server
This query works great:
select id, descr
from SERVER.DB.dbo.TABLE
When I tray to create the view:
|
by: G rumpy O ld D uffer |
last post by:
This is probably a 'Low-Level' question to all the ACCESS experts but
I've only been using ACCESS for a couple of weeks.
I've been given 30+ (and counting) separate 'Weekly' Databases which
all have a Table, in exactly the same Field format, in each of these
'Weekly' DataBases.
I want to set-up a new 'Master' Database, so that I can write one
Query to search all the 'Weekly' Databases and produce one new Table
in the 'Master' Database...
|
by: Robert McGregor |
last post by:
Hi all,
I've got a Front End / Back End database that was working just fine.
One day i opened the FE to find that if I tried to open one of the
linked tables from the database window, nothing happened (hourglass
for about 2 seconds then nothing).
I tried relinking the tables and got the same response. (Access even
completely bombed out once with a Dr Watson failure).
|
by: Joe |
last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource
(using Oracle ODBC drivers). After linking the tables in Access, I inspect
the data contained in the linked tables. For tables that involve a number
field as the primary key, the data is returned successfully. For tables
that involve a character field (e.g. CHAR(3) or VARCHAR(10)) as the primary
key, I have the correct number of rows returned, but the data displayed...
| | |
by: bofh1234 |
last post by:
Hello,
I am having a problem with linked lists. My program is based on a
client server model. The client sends some packets of data to the
server. The server reads those packets and is supposed to store the
data in a linked list. It looks like everything works except for the
fact that the linked list only stores the last value sent and the
number of nodes in the linked list is way to high. For example the
client sends 4 create...
|
by: Grip |
last post by:
Hi,
I have gone throught the group and Microsoft's online help and have
seen many suggestions but I am still seeking clarity:
1. I have an excel spreadsheet. Column A contains text that may be
greater than 255 characters.
2. I have an access database. I link (not import) to the contents of
the excel spreadsheet. In the design view in access, Column A has the
data type "memo".
|
by: MattFitzgerald |
last post by:
As a company we have 2 sites and the same database is replicated at both sites
I link the tables from both these sites into my reporting database using linked tables.
The only problem I have is when I link some tables the link uses the mapping format Y:\ instead of the IP format \\192.239.14.115\.
Is there any way I can replace the Y:\ with the IP format in the Linked Table Manager as I can only use my reporting database on PC's where...
|
by: Arli |
last post by:
I have the following linked tables:
tblMainPL is my main table that I need to pull the information in from. It has the following fields:
Autonumber1 -PK set as autonumber
Date - short date format-example: (10/22/08)
Load Number -Number-Single (should not be higher than 35-40 per day)
WorkOrder -Text- 50 (string of letters and numbers of varying length)-example-KR53103 2625000-48
Badge -text- (employee badge number)-example-c12345
|
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: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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.
| |
