I have one table with an auto-number field which creates an employee ID. I have another table where I want an employees name to appear rather than the ID. How can I convert these ID numbers into a name?
View RepliesOk so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
View 1 Replies View RelatedHello, I have a Field in a Table called Fund Codes and they are entered as text: 1, 2, 3, 4 all the way to 1000. Now i need to find away to change 1 to 001, 2 to 002, 3 to 003, 10 to 010, but leave 100 as 100 and 222 as 222, so only add 0s to numbers from 1 to 99. Is there a way to do it? I know i can change the text to numbers and use format to do that, but i need to leave it as text. Please help. Many thanks!
View 11 Replies View RelatedI need to convert a numbers column in an access table to a text format
View 3 Replies View RelatedI have a form that have textbox that calculate total and then i want to convert numbers to words. Iv got the code with function currencytotext but when i set the code source of another textbox to:
=currencytotext([inv_total])
I have an error. I saw this on internet but i think bcz I am using access 2013 may be the syntax is different a bit.
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
I have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1
1 2
1 3
2 1
2 2
2 3
2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
Example
1 1
2 2
3 3
2 1
2 2
2 3
I have a table with fields like this one but the weeks go all the way up to 52. What I am trying to do is count the number of consecutive zeros and if it is more than five, count how many of the following fields have a number in them and if that number is less than the number of zeros preceding it identify that person.
For example Joe would be identified below because he had 6 consecutive zeros and then he had 5 weeks of numbers immediately following the string of zeros. Bob would not be identified because he had 5 consecutive zeros and then 5 sets of numbers immediately following the string of zeros so the zero frequency isnt higher than the number frequency immediately following.
ID
Name
Date of Hire
1
2
3
4
5
[code]...
Maybe someone could point me in the right direction:
What I am trying to do is convert a XLS file to CSV by the press of a button, how would I go about doing this?
Cheers
Will
Ineed a Db converted to 97 can some one do it for me.
I only have 97 on my machine at home
I have the following SQL Expression :
SELECT tblDeal.DealCode, UCase([HirerForename] & " " & [HirerSurname] & " " & [HirerBusinessName]) AS FullHirerName, UCase([DealerName] & " ( " & [DealerTownCity] & ")") AS FullDealerName, tblHirer.JointHirer, tblDeal.DateDealIn, UCase([CurrentStatus]) AS FullCurrentStatus, UCase([CurrentFinanceCo]) AS FullCurrFinCo, UCase([LastUser] & " / " & [LastUserDate]) AS FullLastUser, tblDeal.InUse, tblVehicles.VehicleRegNo, tblDeal.DealType, tblDealer.DealerName, tblDeal.LastUpdatedDate, tblDealer.DealerName, tblDeal.DateDealIn, tblRep.BrokerCode, tblHirer.HirerCurrPostcode, tblDealer.DealerTownCity, tblDealer.DealerFranchise1, tblDealer.DealerFranchise2, tblDealer.DealerFranchise3, tblDeal.LastUser, tblDeal.LastUserDate, tblHirer.HirerSurname, tblHirer.HirerBusinessName, tblHirer.HirerForename, tblHirer.ProposalType
FROM tblVehicles INNER JOIN (((tblBroker INNER JOIN ((tblRep INNER JOIN tblDealer ON tblRep.RepCode = tblDealer.RepCode) INNER JOIN tblDealerContacts ON tblDealer.DealerCode = tblDealerContacts.DealerCode) ON tblBroker.BrokerCode = tblRep.BrokerCode) INNER JOIN tblHirer ON tblDealerContacts.DealerContactCode = tblHirer.DealerContactCode) INNER JOIN tblDeal ON tblHirer.HirerCode = tblDeal.HirerCode) ON tblVehicles.VehicleCode = tblDeal.VehicleCode
WHERE (((tblDeal.DateDealIn)>=[Forms]![frmSearchCustomer]![Text110]) AND ((UCase([CurrentStatus]))<>"Deleted") AND ((tblVehicles.VehicleRegNo) Like "*" & [Forms]![frmSearchCustomer]![Combo96] & "*") AND ((tblDealer.DealerName) Like "*" & [Forms]![frmSearchCustomer]![Combo92] & "*") AND ((tblRep.BrokerCode)=[Forms]![frmIntro]![BrokerCode]) AND ((tblHirer.HirerCurrPostcode) Like "*" & [Forms]![frmSearchCustomer]![Combo100] & "*") AND ((tblHirer.HirerSurname) Like "*" & [Forms]![frmSearchCustomer]![Combo29] & "*"));
What I would really like to do is tell the query to dislpay YES or NO instead of 0 or -1 in the Joint Hirer field. Can anyone help please
Thanks
We have both Access 2003 and Access 97 users on our network and i have a database that is written in 97. But lately we've been getting unrecognized format when we open it. So my guess is someone out there is opening it in 2003 and converting it. Is there a way to tell the database to ONLY run on 97 and DO NOT convert to a different version?
Thanks,
Rick
Hi
This is my 1st post so please be gentle
I am running an Access Database for a large uk companys call centre.. I use many diffrent systems which allow me to show our centres productivity...
I have hit a wall
When i first developed the DB i used the time formatting hh:mm:ss but later down the line i found if you *the time by 86400 it converts the hh:mm:ss into a workable seconds format....
One of my old extracts that i feedinto the DB are in the old format therefor i do this calculation within an access query which *the time by 86400
however i have found an probelm.. if the time is over 24hrs it seems to double the time there or there abouts in seeconds
example
01/01/1900 01:42:00 which is basically 25 hrs should be around 92520 seconds
when is do this calculation in an access query it is coming out 178920 all the other times are correct so this rules out how i link 2 querys ....
any ideas???
Hi, hope someone here has experience with this issue.
Our system was recently converted to much tighter secured network environment and moved to Office 2003 (still Windows XP).
Within the next couple of weeks we expect to split/secure our new db for shared use across a network (approx. 6-8 users at any one time).
I want to keep the db in 2000 as long as possible because I'm doing lots of the work at home and that's what I have. After deployment there will be lots more changes but in managed releases. (sounds official, eh?...)
Does anyone know of any risks I'm running by keeping it in 2000 within Access2003?
Thanks very much,
Hi.
I have a db with a numeric field called Eur_2.
This field is populated with data imported from an excel file.
When the Eur_2 excel field contain Null, in the correspondent Access Field the value inserted is 0 (zero).
I have necessity to put in the Access field Null value.
The VBA code that I use for import is:
Eur_2 = XL.Application.Cells(indriga, indcolonna + 10)
If Eur_2 = 0 Or _
Eur_2 = Empty Then
Eur_2 = Null
End If
but when i open the Access table, the value inserted is 0 (zero).
There's something to do for change 0 (zero) to Null?
Thanks
Luigi
Hello
It been a while since i have been on the forum. I developed a database last year and since then i have moved to a MAC.
I would like to know if there is a quick and easy way of converting a database to application which works on any PC or MAC which does not have access?
When I load a spreadsheet I can't add an index because it is a linked table, so I want to save it in MDB format so that I can add an index and then export it to SQL. However, when I save the file as an MDB file the data is all still linked as a spreadsheet and I still can't add an index. (I would add the index at the last step in SQL, but Pervasive SQL is very temperamental.)
Basically I just want to export an Excel spreadsheet into SQL and add an index.
Hi All.
In my calculation in a query:
Cost: Sum([Cost]/12*(DateDiff("m",[StartDate],Now())))
Cost is Currency;
StartDate is Date.
The result look like Numeric format I would like to have Currency format. Is it possible?
Thanks.
I have a query in which I am splitting a long integer into Month, Day, Year and then putting it back together using Date Serial in another query. When I originally set this up, the date was coming through as 5 numbers. Now it is coming through as six. However, Access will error out if I try to set it as a date.
So, how can I redo the fields below to get 2 digits for the month instead of one. The date now comes through as 041706
CInt(Mid([Field1],1,2)) AS iMo,
CInt(Mid([Field1],2,2)) AS iDay,
CInt(Mid([Field1],4,2)) AS iYear
Looked through DateSerial but possibly not the way to go. I have a text field, "Month", with the dates in this format: Jan-06, Feb-06. What do I use to convert these to date format? 1/1/06, 2/1/06.
Thanks
Toni
Hey Guys,
Having some trouble doing a couple of things using both Data Definition Language and a Right() Query.
What I am trying to do is convert this field:
August 17, 2006 3:56 PM
into this
15:56:00
Using this:
Right([Travel Date/Time To],InStr([Travel Date/Time To],",")-1)
Now because the time part in my first field can vary from being 3:56 PM to 12:56 PM Right() is only getting those where the time has two digits.
I tried to use DDL to convert my field to DATETIME but cannot convert two columns in one SQL statement.
Can anyone help me out with this? Sorry if the details are sketchy but I'm being kicked out of the office (after 5:00pm you see!).
Cheers.
Hi,
I have a query that outputs results as follows:
Expr1 Expr2 Expr3 Expr4 Expr5
1 2 3 4 5
I want to write another query to output the above result as follows:
Expr
1
2
3
4
5
Does anyone know how to do this please? Thanks!
Hi all,I'v been at this for awhile nowI did a search and tried out some of the examples but for some reason they didnt work.Heres the format i have it in. D| O | B04 | 30 |1967 all in a text fieldI need to convert it into 4/30/1967I tried creating a new field called DOB and setting it to date/time and then using a concate query but it said it could not convert it.UPDATE Table1 SET Table1.DOB = [D] & "'/" & [O] & "/" & [B];UPDATE Table1 SET Table1.DOB = "#" & [D] & "'/" & [O] & "/" & [B] & "#";Please helpTake care
View 5 Replies View RelatedMy query is...
SELECT tblExtensions.Extensions AS 'PIN', tblExtensions.Budget AS 'LIMIT',
tblExtensions.Extension is text
tblExtensions.Budget is number
I would like to convert the tblExtensions.Budget to a text/character filed for further manipulation.
In SQL it all works, how do I change this in MS Access?
Hi Guys, i have a problem in converting NULL to 0 , i wrote a query which gave me a Null value, But i would like to know if there is any way i can change the NUll value to 0.
ex
SELECT Sum(xxxx) AS yyyy FROM zzzzz WHERE******=#####;
i got NULL.
I need to get 0 instead of Null.
Plz help
I have created a database application using forms and reports as user interface. Now I have to make that database available on the internet. I was wondering if there is a way to convert the forms into active server pages without loosing any event handling code. Or do I have to design the pages from the scratch?
Will appreciate any pointers. Thanks.
GreetInfo