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
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
Hi,
I would like to convert zero if enter into null value during the data entry phase in a form. This means that zero values will be stored as null values. Is there a way? I am not able to do it in the input mask somehow.
Thanks for any help in advance.
Simple query, but I need to roll the results up with others and a null return is hurting me. I have been chasing my tail around with the nz(), input masks, IIF statements, only to prove my ignorance and possible insanity for expecting different results.
Here we go:
SELECT Count([LDB LOG].DSTHTL) AS ["tot120"]
FROM [LDB LOG]
WHERE ((([LDB LOG].[LDB Rcvd]) Between Now()-120 And #1/1/2008#))
GROUP BY [LDB LOG].[T-7 Date]
HAVING ((([LDB LOG].[T-7 Date]) Is Null));
This is being used to count open items into age buckets, the above is the greater than 120 days (discounting anything prior to 2008). If the T-7 Date is null the item is open. What is happening is that no records are being selected, resulting in the null value back.
Any help would be greatly appreciated as I have spent an unrealistic amount of time on this.
Thanks
Brendan
I have a query which returns, among other things, a number of boolean fields. In some cases, there will be a genuine True or False value in each of these fields; in others, it can and should be Null (e.g. as a result of a 'failed' LEFT JOIN of some description, where there is no associated record in the joined table which fulfills the criteria)
So something like this :
Code:
SELECT [tblTable2].[fldBooleanField]....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, I will be writing the result of the query to a text file and here's the problem. I want to show a numeric value for a genuine True / False (i.e. -1 and 0 respectively using the standard boolean conversions in Access) and a blank for any Null values.
So I tried this :
Code:
SELECT CInt([tblTable2].[fldBooleanField]) AS fldBooleanField....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, currently when I look at the exported recordset in Notepad, I am getting 0's for both False and Null values (and -1 for True)
How I can adapt my query to keep Nulls...null? And convert the genuinely present boolean values to integer form?
Only thing I can think of is to use (untested) :
Code:
IIf([tblTable2].[fldBooleanField] Is Null, Null, CInt([tblTable2].[fldBooleanField]))
But there's a number of boolean fields in there, all requiring the same treatment.
I do not understand what is happening here. I have foll0wing line in a calculated query field:
m: Switch([EmpID]<5,1) ' run Query 18 in attached example, A2007/2010
this produces 1 for all EmpID<5 and Null for all other EmpID's. All as expected.
But if I do this:
m: Switch([EmpID]<5,1,[EmpID]>=5,Null) ' run Query 19 in attached example
then the entire column is set to Null
It might be an easy one but I just wasted the past hour deciphering through my code in order to solve the run-time error '94' that I'm getting when trying to execute the following code:
Code:
Private Sub cmdUpdateDates_Click()
'###################################
'This sub aims at combining the timesheet date and the start and end time into the fields [Start Time] and [End Time].
'###################################
Dim intCounter As Integer
intCounter = 0
Dim rs As ADODB.Recordset
[Code] ....
Hello all,
A bit of a weird one, I've got a query and the criteria for showing records is that one particular field is null. However the query is showing records with the values in the field chosen for the Is Null.
Not sure why this is happening, has anyone come across this problem before?
Thanks.
I am having problems with setting up a set of combo boxes.
What I am trying to do is if combo Productline is empty then in combo PartNumber would show all products but if combo Productline has a value selected then in the combo partnumber would only be able to select the partnumbers in that productline.
Hi, I have some problem with assigmnet with date and string variable. what i wana do is get data from Forms textboxes into variable and then by insert query send to history table.
the problem occurs when there is blank textbox its says invalid use of null.
e.g
myStringVariable = Forms!myform!EmpName
myDateVariable = Forms!myform!EmpDOB
this code is behind the update button which i press when ever i want to shift data to History table
so when the fields are empty the invalid use of null error arrise
any idea how to handle this null specially in date
I think the title pretty much sums it up....
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
The query is refusing to return any results...
Any ideas?
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,
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?