In a previous post someone mentioned a difference between Null and Empty. Is Null is not pulling the nulls but I know those fields are empty. Can someone tell me how to pull in these empty fields. for my calculation I need to get Companies that showed revenue after and didn't show revenue once their contract started.
Thank you
I have the following problem:
I have a make table query that sum a total amount in order to have only the total in the table field.
However, when the total is 0.00, the make table query says "you are about to pos 0 rows" instead of postin 1 row with 0.00.
How can I set the query in order to have a 0 in the table when the total is 0 instead of having nothing posted ?
I have tried the is null, is empty, is missing, nz and nothing seems to work.
I have the query below and it returns the number of cases for eache of the case status (open, closed or private) Some of the cases have no status, the field is empty. Is there a way to count the number of cases which have no status in the status field?
Can someone tell me how to do this?
SELECT
Count([Report table part one].CaseStatus) AS CASES,
[Report table part one].CaseStatus AS STATUS
FROM [Report table part one]
GROUP BY [Report table part one].CaseStatus;
I have a form, form1. Inside there is a combo box, cbo1, that gets its values from table1.On form load, I want an expression that checks if table1 has any values to "send" to cbo1. If yes, meaning, if after form loads cbo1 is loaded with table1's values, I want cbo1 colored RED, if not, colored WHITE.
View 14 Replies View RelatedI have a module that creates records into a contacts table in my access database. When there is no data in the field from outlook, the data is populated as an empty string "" instead of a null value, creating problems with subsequent queries and processes. I'd like to create the data as a null value but the module keeps failing with "object is required" on the statement
rstImport("Customer ID").Value = System.DBNull.Value
It doesn't seem to like System.DBNull.Value or DBNull.Value, and when I assign the vbNullString constant instead, it gets set to "" again and not null.
Im trying to concatenate a bunch of fields (50 arghh) which each are either blank or just contain one letter. This was someone elses setup for an attendance register which I think is an odd way of doing it. I would have used one field and then to get the mark for a week take a substring at the appropriate position. Anyway im trying to replace an empty string field with a letter to represent the register hasn't been marked using sql statement:
SELECT [400 Student Marks].acad_period, [400 Student Marks].student_id, [400 Student Marks].register_id, [400 Student Marks].register_group, Replace([1],"","U") AS attendance
FROM [400 Student Marks];
[1] being the first register week then I would have concatenated with [2] etc...
This however causes an error each time on the attendance field so im guessing replace doesn't work on empty strings. Is there a way round this/alternative.
Thanks for any advice.
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?
Hello World:I have two tables, one called Assets and the other table called Job Sites. the Assets table has several fieldsAssetID - autonumberplus othersthe Job Sites table has two fieldsJobSiteID - autonumberJobSite - text (with about 22 names in it)the I add the JobSite to the Assets table (hiding the JobSiteID) it shows me the expected drop down box but it is BLANK. If I click on any of the BLANK spaces in the drop down, it populates the field. It is behaving as if the font color was white.Any ideas??? I am baffled. :confused: :confused:
View 8 Replies View RelatedHello,
I have just created a query so that I may base my report on it. The query ties together 4 tables. I only included the fields that will be in my report. I created the report using the wizard and used my new query as the source but it does not want to work. I tried opening the query and it was empty even though I have entries in the database.
ANY IDEAS :confused:
Dear All,
I'm trying something quite simple, adding dates into a table with the aid of an action SQL statement (INsert Into) in VBA.
However, if a date is empty, the value 12:00:00 AM is filled in! I really want that if it a date is set to empty, that noting is added to the table... I can use an if statement though, to fix it, but why does it fill in 12:00:00 AM if a date is empty?? Can you fix it? Or is there no other way?
I use Access 2003.
tnx,
Zurvy :eek:
I have a query that appends records to a table with a constant numer of fields. so sometimes I have fields with no data. is there any way to create query which selects only fields with data.
thanks in advance
I am trying to make a query with two tables. Each row consist of a student ID, their name and all their personal information. Both tables are exactly the same. But one table is a link table. That link table gets update every so often from an outside program and the other table is not linked. I want to be able to run a query that updates certain fields called "address changes". I have figured out how to find the updates, but I am having a problem with a field that is blank. If I have a field in the non-linked table that is empty but there is information in the linked table, it does not see it. Is there a way to get around this empty field problem?
View 3 Replies View RelatedI have been going back in a db to the beginning of time and restructuring...I have a field sales person if its null I need to assign a value of 100 to another field representativeid, I have been using the update query to accomplish the update until now. I've tried is null and "" to get this query to update, any idea?
View 4 Replies View RelatedI have an Input form that does not clear the fields after I hit the Save button. Data Entry is set to YES. If I use the navigation bar, there is no problem, but I would like to have the fields empty after hitting the Save Button, so that the navigation bar can be removed. What is wrong?
View 4 Replies View RelatedHi guys, here is my new problem.I have a form which conatins a subform that shows info from a query.I created a query which shows records from table history when the field comment is like *code 1.the criteria in the query, in the comment field is: [forms]![edititem]![vconcate]In the edititem form i have a hidden field called vconcate which receives the value as follows:me.vconcate= "*code " & me.winecode (me.winecode is integer)after assigning the value to vconcate i display the vconcate value and shows*code 1, as it should be. However, the subform doesnt displays any record.I also tryed to insert the query in the editform, but nothing.if i run the query from the query window, and fill the value as *code 1, it works.here is more info:when the edititem is open, the user has to select a wine code from a combo box. After this selection the values are assigned to me.winecode and the me.vconcate. Because when i open the form edititem these codes are null or empty the subform is empty. Is there any way to refresh the subform via code?any suggestion?
View 1 Replies View RelatedHey guys......simple problem
I have a form with 3 combo boxes.
The 1st combo, called winecode, gets the values from the following sql:
SELECT [winecode] FROM wine WHERE winedesable=0;
it works.....
The second combo box, called suppname, gets the values from another select, but depending on the previous combo:
SELECT DISTINCT [supplier].[suppname] FROM supplier, suppwine WHERE [supplier].[suppnumber]=[suppwine].[suppnumber] And [suppwine].[winecode]=[Forms]![addpop]![winecode];
it works......
The 3 rd combo, called winesubcat, gets the values from a query which takes the selected value from the previous 2 combos. The query works by it self . However, i added the query in the data tab and open the form. then i selected values from the first 2 combos and there is no value in the 3rd combo...... any idea??
thx max.
When I click a button I have code (below) that checks to see if my Combo boxes are NULL (empty), and if they are it’ll prompt the user to “fill in all fields”. Right now it includes all of the combo boxes on my form. Although, I want it to only include 3 combo boxes (I have 5 on my form). How could I do this, lets say if my combo box names are cbo1, cbo2, cbo3 ?
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acComboBox
If ctl.Value & "" = "" Then
MsgBox "You must fill in all data fields."
Exit Sub
End If
End Select
Next
Hello, I have writen a piece of code:
Code:DoCmd.RunSQL "SELECT tblTrades.tradeID, tblTrades.TradeConfirm, tblTrades.Laurent FROM tblTrades WHERE (((tblTrades.TradeConfirm)=No) AND ((tblTrades.Laurent)=Yes));"
How do I check to see if it returns any results?
Help.
I can seem to find a way to test if the SQL for my record set returns value. I've tried using the if statement with .BOF and .EOF. When there are no records returned for the criteria, there should be a way to test it with IF. I can't seem to figure it out. Using the record count does not work because it also returns at least one record. Help.
Here is the code
Dim rsContacts As ADODB.Recordset
Set rsContacts = New ADODB.Recordset
SQLStr = "SELECT FormatPercent(((43200-Sum(DateDiff('n',[tblOutageDetail.StartTime],[tblOutageDetail.endtime])))/43200),2) AS ElapsedTime FROM (qryOutageTotalMin INNER JOIN tblOutageData ON qryOutageTotalMin.Outage = tblOutageData.Outage) INNER JOIN tblOutageDetail ON tblOutageData.Outage = tblOutageDetail.Outage WHERE (((tblOutageData.System)='PARK PRIMARY') AND ((tblOutageDetail.StartTime)>=Date()-30) AND ((tblOutageDetail.OtgCat)=1))"
With rsContacts
.Open SQLStr, CurrentProject.Connection, adOpenStatic, adLockReadOnly
End With
'Me!btnparkpri.Caption = CStr(rsContacts!ElapsedTime)
'Set rsContacts = Nothing
If rsContacts.RecordCount = 1 Then
Me!btnparkpri.Caption = CStr(rsContacts!ElapsedTime)
End If
If Me!btnparkpri.BackColor = 255 Or Me!btnparkpri.BackColor = 32768 Then
Me!btnparkpri.ForeColor = 16777215
End If
Set rsContacts = Nothing
I am trying to make a query with two tables. Each row consist of a student ID, their name and all their personal information. Both tables are exactly the same. But one table is a link table. That link table gets update every so often from an outside program and the other table is not linked. I want to be able to run a query that updates certain fields called "address changes". I have figured out how to find the updates, but I am having a problem with a field that is blank. If I have a field in the non-linked table that is empty but there is information in the linked table, it does not see it. Is there a way to get around this empty field problem?
Thanks
Hello visitors,
I have a report base on a select query but in the report i get the following:
page 1 = record 1
page 2, page 3, page 4 = are empty (nothing to see on the pages)
page 5 = record 2
page 6, page 7, page 8 = are empty (nothing to see on the pages)
page 9 = record 3
...........etc...
So there are three empty pages between every two pages(records).
I have checked in the settings of the report and all properties but cannot find the solution.
Can any one help me to solve this ?
Thanks in advance