I have a database setup that uses ODBC connections into SQL for read
only views to compare information from multiple sql databases in our
company. Most of the tables use the Employee ID as the primary key.
The problem is, one database formats the EmpID with leading zeros so
all EmpID's are 5 characters long. (ie, 00123, 01523) Another
database formats the EmpID as just the number of characters as the
number is (ie, 123, 1523). When you try to create the relationship
between the two tables - you can't because of the inconsistency. (the
fields are formatted as text fields in the original dbs)
I want to create a query that will create a new field to add the
preceeding zeros to the EmpID. This is where I need assistance. WHat
is the format code for forcing those preceeding zeros into a field?
1  27742 
On Jun 4, 3:51*pm, LFM <cruzen...@gmai l.comwrote:
I have a database setup that uses ODBC connections into SQL for read
only views to compare information from multiple sql databases in our
company. * Most of the tables use the Employee ID as the primary key.
The problem is, one database formats the EmpID with leading zeros so
all EmpID's are 5 characters long. *(ie, 00123, 01523) Another
database formats the EmpID as just the number of characters as the
number is (ie, 123, 1523). *When you try to create the relationship
between the two tables - you can't because of the inconsistency. *(the
fields are formatted as text fields in the original dbs)
I want to create a query that will create a new field to add the
preceeding zeros to the EmpID. *This is where I need assistance. *WHat
is the format code for forcing those preceeding zeros into a field?
If you want to force leading zeros to be displayed, then the format
function will do the job -
Format(EmpID, "00000")
If you want to output a string holding this, then wrap a CSTR function
around it all:
cstr(Format(Emp ID, "00000"))
Does this help?
Regards
Andrew This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: LRW |
last post by:
This may be a question more directed to Excel, but I'm using PHP to
generate a CSV to be imported into Excel, so maybe the solution is
there. So, sorry for crossposting.
Anyway, I'm generating a text file, a CSV, that contains addresses
from a database. Zip codes that have a leading zero (eg: 01234) has
that 0 in the database, and it appears to have it when it's being
inserted and written to the text file, but when you view it in Excel...
|
by: yanir |
last post by:
Hi
I use reponse.contenttype = "application/vnd.ms-excel"
So the browser will show the data in excel format, but for
some fields I use leading zero's, which truncated by the
browser, at this content type.
If I concat a "'", or other none numeric char, the problem
is solved inconsistently!!!
But even this solution is problematic - is there any
solutions, explanation?
|
by: Jeff Lowry |
last post by:
I'm pasing a zip code as a prameter to an Access stored procedure. In
Access the parameter is a text data type. It works for non-leading zero
zip codes but, apparently access (or ASP) is converting it to a value
first (dropping the zero) then sending that to my SP. Even if I use
cStr() to be sure the parameter is sent a string it still seems to drop
the leading zero. Any thoughts? Note: It needs to be a string for
canadian zip...
|
by: david |
last post by:
Hi,
I have 2 text boxes on an ASP form.
A user enters a Serial Number in TB1 such as 0105123456, presses tab to
move to TB2, TB2 then displays the value of TB1 after a calculation has
been done.
(Based on a Serial Number range).
i.e. a user enters 0105123456, TB2 then adds 'x' qty to this number
depending on how many serial numbers are required.
|
by: Joshua Ammann |
last post by:
Hello,
I'm trying to export a query containing contact information, including a
field. Some zip codes have one or two leading zeros, for example,
San Juan, PR (00927) and Springfield, MA (01104). When I export to a comma
separated value (.csv) file, the zip code for San Juan becomes "927" and for
Springfield becomes "1104". How can I prevent the leading zero(s) from being
trimmed in the exported .csv file?
Because the table contains...
| | |
by: GarryJones |
last post by:
I have code numbers in 2 fields from a table which correspond to month
and date.
(Month, Code number)
Field name = ml_mna
1
2
3
etc up to 12
(Data is entered without a leading zero)
|
by: FAQ server |
last post by:
-----------------------------------------------------------------------
FAQ Topic - How can I see in javascript if a web browser accepts cookies?
-----------------------------------------------------------------------
Writing a cookie, reading it back and checking if it's the same.
http://www.w3schools.com/js/js_cookies.asp
Additional Notes:
|
by: Andrew Poulos |
last post by:
In my limited testing with FF 2, IE 6 and Opera 9 when I divided a
positive integer, that is less than 100, by 100 I get a leading zero in
front of the decimal point.
For example 80/100 gives 0.8
This is what the server app is expecting a leading zero (it does indeed
fail without it). Do I need to do anything to ensure the leading zero is
there?
|
by: bobm2005 |
last post by:
Whatever format I try in Printf, an 'E' format number nearly always
has a leading non-zero:-
1.2345E7
-9.3456E8 etc.
Is it possible to force it (printf) always to have leading zero?
Thus, the above becomes:-
|
by: lornab |
last post by:
Good Afternoon
I need a column to show in a report as 10 digits long by adding zero's to the left of the result. My select statement is like this:
SELECT
a.client,
a.apar_id
FROM agltransact a
where a.client = 'SD'
|
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: 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: 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: 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: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
