NULL DATE

This works fine if there is a date in the textbox, but if the textbox is 
empty or null, I get an error.  This is part of an SQL statement.  What can 
I do if it's Null so that it still works.
Thanks
DS

Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#") 


0
DS
2/10/2008 6:33:07 PM
access.formscoding 7493 articles. 0 followers. Follow

15 Replies
742 Views

Similar Articles

[PageSpeed] 40

Change you line to

Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#"))

This will return a zero legnth string instead of a null value, and will 
allow your code to process it.

Note that if at any point you are comparing that field to Null, it will 
fail, and you will have to compare it against "" instead.

"DS" wrote:

> This works fine if there is a date in the textbox, but if the textbox is 
> empty or null, I get an error.  This is part of an SQL statement.  What can 
> I do if it's Null so that it still works.
> Thanks
> DS
> 
> Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#") 
> 
> 
> 
0
Utf
2/10/2008 6:50:00 PM
I tried this, doesn't work.
Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#"), 
"Null")
DS 


0
DS
2/10/2008 6:50:43 PM
Remove Null and leave just the two quotes.

If that doesn't work, let me know what error you are getting.

"DS" wrote:

> I tried this, doesn't work.
> Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#"), 
> "Null")
> DS 
> 
> 
> 
0
Utf
2/10/2008 6:59:00 PM
Thanks, I'm receiving an Error message 3134.  Here it is in its entirity.
DSQL = "INSERT INTO tblDiscounts " & _
                "(DiscountID,DiscountName,DiscountDP,DiscountAmount,DiscountOpen,DiscountActive,DiscountExpire,DiscountExpDate,DiscountAction,DiscountTypeID,DiscountWhere,DiscountReportID) 
" & _
"Values(" & Forms!frmBSDiscountNames!TxtID & ", " & _
"" & Chr(34) & Forms!frmBSDiscountNames!TxtName & Chr(34) & ", " & _
"'" & 1 & "', " & _
"'" & Forms!frmBSDiscountNames!TxtAmount & "', " & _
"" & Forms!frmBSDiscountNames![ChkOpen] & ", " & _
"" & Forms!frmBSDiscountNames![ChkActive] & "," & 
Forms!frmBSDiscountNames![ChkExp] & ", " & _
"" & Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#")) & 
", " & _
"'" & Forms!frmBSDiscountNames!TxtDiscountAction & "', " & _
"" & Forms!frmBSDiscountNames!TxtDiscountTypeID & ",'" & 
Forms!frmBSDiscountNames!TxtDiscountWhere & "','" & 
Forms!frmBSDiscountNames!TxtReportID & "')"
DoCmd.RunSQL (DSQL)

DS 


0
DS
2/10/2008 7:06:20 PM
This doesn't work either.

Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#"),"Null") 
AS DiscountExpDate

DS 


0
DS
2/10/2008 7:07:40 PM
INSERT ERROR 3134
Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#"), "")
Thanks
DS 


0
DS
2/10/2008 7:11:20 PM
Try:

Dim DiscountExpDate As String
DiscountExpDate = Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], 
"\#mm\/dd\/yyyy\#"),"")



"DS" wrote:

> This doesn't work either.
> 
> Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#"),"Null") 
> AS DiscountExpDate
> 
> DS 
> 
> 
> 
0
Utf
2/10/2008 7:14:00 PM
I didn't see your post with the code before I replied.  Here is my 
recommendation with the update placed in your code.


Dim DiscountExpDate As String
DiscountExpDate = Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], 
"\#mm\/dd\/yyyy\#"),"")


DSQL = "INSERT INTO tblDiscounts " & _
"(DiscountID,DiscountName,DiscountDP,DiscountAmount,DiscountOpen,DiscountActive,DiscountExpire,DiscountExpDate,DiscountAction,DiscountTypeID,DiscountWhere,DiscountReportID)" & _
"Values(" & Forms!frmBSDiscountNames!TxtID & ", " & _
"" & Chr(34) & Forms!frmBSDiscountNames!TxtName & Chr(34) & ", " & _
"'" & 1 & "', " & _
"'" & Forms!frmBSDiscountNames!TxtAmount & "', " & _
"" & Forms!frmBSDiscountNames![ChkOpen] & ", " & _
"" & Forms!frmBSDiscountNames![ChkActive] & "," & 
Forms!frmBSDiscountNames![ChkExp] & ", " & _
DiscountExpDate & ", " & _
"'" & Forms!frmBSDiscountNames!TxtDiscountAction & "', " & _
"" & Forms!frmBSDiscountNames!TxtDiscountTypeID & ",'" & 
Forms!frmBSDiscountNames!TxtDiscountWhere & "','" & 
Forms!frmBSDiscountNames!TxtReportID & "')"
DoCmd.RunSQL (DSQL)



"DS" wrote:

> This doesn't work either.
> 
> Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#"),"Null") 
> AS DiscountExpDate
> 
> DS 
> 
> 
> 
0
Utf
2/10/2008 7:21:01 PM
Hi Thanks,
This I'm a little confused on.  The Dim I know where to put, But what goes 
in the SQL statement.
Thanks
DS 


0
DS
2/10/2008 7:26:40 PM
OK, I tried it, Works fine with a date but without a date I get an Insert 
message 3134.
Thanks
DS 


0
DS
2/10/2008 7:32:30 PM
To be honest, SQL statements aren't my strong point.  If you are appending 
data on your form to a table, you could use the rst commands.  That's what I 
use most of the time.  I'll look into your SQL string and get back to you 
shortly.

"DS" wrote:

> Hi Thanks,
> This I'm a little confused on.  The Dim I know where to put, But what goes 
> in the SQL statement.
> Thanks
> DS 
> 
> 
> 
0
Utf
2/10/2008 7:49:02 PM
No. Dates are numeric, so you can't use "" as a possible value. Not only 
that, but the Nz function isn't appropriate as written: Format returns a 
string, so there's no point in using Nz on the result of the function.

What's needed is

DiscountExpDate = IIf(IsNull(Forms!frmBSDiscountNames![TxtExpDate]), "Null", 
Format(Forms!frmBSDiscountNames![TxtExpDate],"\#mm\/dd\/yyyy\#"))

or, easier,

DiscountExpDate = 
Format(Forms!frmBSDiscountNames![TxtExpDate],"\#mm\/dd\/yyyy\#;;;\N\u\l\l"))


-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Scott Whetsell, A.S. - WVSP" 
<ScottWhetsellASWVSP@discussions.microsoft.com> wrote in message 
news:9415EB82-1BF4-4302-9A6C-572B01D3F245@microsoft.com...
>I didn't see your post with the code before I replied.  Here is my
> recommendation with the update placed in your code.
>
>
> Dim DiscountExpDate As String
> DiscountExpDate = Nz(Format(Forms!frmBSDiscountNames![TxtExpDate],
> "\#mm\/dd\/yyyy\#"),"")
>
>
> DSQL = "INSERT INTO tblDiscounts " & _
> "(DiscountID,DiscountName,DiscountDP,DiscountAmount,DiscountOpen,DiscountActive,DiscountExpire,DiscountExpDate,DiscountAction,DiscountTypeID,DiscountWhere,DiscountReportID)" 
> & _
> "Values(" & Forms!frmBSDiscountNames!TxtID & ", " & _
> "" & Chr(34) & Forms!frmBSDiscountNames!TxtName & Chr(34) & ", " & _
> "'" & 1 & "', " & _
> "'" & Forms!frmBSDiscountNames!TxtAmount & "', " & _
> "" & Forms!frmBSDiscountNames![ChkOpen] & ", " & _
> "" & Forms!frmBSDiscountNames![ChkActive] & "," &
> Forms!frmBSDiscountNames![ChkExp] & ", " & _
> DiscountExpDate & ", " & _
> "'" & Forms!frmBSDiscountNames!TxtDiscountAction & "', " & _
> "" & Forms!frmBSDiscountNames!TxtDiscountTypeID & ",'" &
> Forms!frmBSDiscountNames!TxtDiscountWhere & "','" &
> Forms!frmBSDiscountNames!TxtReportID & "')"
> DoCmd.RunSQL (DSQL)
>
>
>
> "DS" wrote:
>
>> This doesn't work either.
>>
>> Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], 
>> "\#mm\/dd\/yyyy\#"),"Null")
>> AS DiscountExpDate
>>
>> DS
>>
>>
>> 


0
Douglas
2/10/2008 8:04:55 PM
Excellent point Doug, didn't even think of that yet.  DS let us know if it 
works.  Also check your SQL string and make sure that you aren't trying to 
write to an AutoNumber field.

"Douglas J. Steele" wrote:

> No. Dates are numeric, so you can't use "" as a possible value. Not only 
> that, but the Nz function isn't appropriate as written: Format returns a 
> string, so there's no point in using Nz on the result of the function.
> 
> What's needed is
> 
> DiscountExpDate = IIf(IsNull(Forms!frmBSDiscountNames![TxtExpDate]), "Null", 
> Format(Forms!frmBSDiscountNames![TxtExpDate],"\#mm\/dd\/yyyy\#"))
> 
> or, easier,
> 
> DiscountExpDate = 
> Format(Forms!frmBSDiscountNames![TxtExpDate],"\#mm\/dd\/yyyy\#;;;\N\u\l\l"))
> 
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
> 
> 
> "Scott Whetsell, A.S. - WVSP" 
> <ScottWhetsellASWVSP@discussions.microsoft.com> wrote in message 
> news:9415EB82-1BF4-4302-9A6C-572B01D3F245@microsoft.com...
> >I didn't see your post with the code before I replied.  Here is my
> > recommendation with the update placed in your code.
> >
> >
> > Dim DiscountExpDate As String
> > DiscountExpDate = Nz(Format(Forms!frmBSDiscountNames![TxtExpDate],
> > "\#mm\/dd\/yyyy\#"),"")
> >
> >
> > DSQL = "INSERT INTO tblDiscounts " & _
> > "(DiscountID,DiscountName,DiscountDP,DiscountAmount,DiscountOpen,DiscountActive,DiscountExpire,DiscountExpDate,DiscountAction,DiscountTypeID,DiscountWhere,DiscountReportID)" 
> > & _
> > "Values(" & Forms!frmBSDiscountNames!TxtID & ", " & _
> > "" & Chr(34) & Forms!frmBSDiscountNames!TxtName & Chr(34) & ", " & _
> > "'" & 1 & "', " & _
> > "'" & Forms!frmBSDiscountNames!TxtAmount & "', " & _
> > "" & Forms!frmBSDiscountNames![ChkOpen] & ", " & _
> > "" & Forms!frmBSDiscountNames![ChkActive] & "," &
> > Forms!frmBSDiscountNames![ChkExp] & ", " & _
> > DiscountExpDate & ", " & _
> > "'" & Forms!frmBSDiscountNames!TxtDiscountAction & "', " & _
> > "" & Forms!frmBSDiscountNames!TxtDiscountTypeID & ",'" &
> > Forms!frmBSDiscountNames!TxtDiscountWhere & "','" &
> > Forms!frmBSDiscountNames!TxtReportID & "')"
> > DoCmd.RunSQL (DSQL)
> >
> >
> >
> > "DS" wrote:
> >
> >> This doesn't work either.
> >>
> >> Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], 
> >> "\#mm\/dd\/yyyy\#"),"Null")
> >> AS DiscountExpDate
> >>
> >> DS
> >>
> >>
> >> 
> 
> 
> 
0
Utf
2/10/2008 8:30:00 PM
Thanks Douglas, AGAIN!  I see the error of my ways. I used your statement in 
my SQL statement and it works great.  Once again I appreciate the help!
DS 


0
DS
2/10/2008 8:48:57 PM
Thanks Scott I appreciate your help.  I used Dougs suggestion and I am up 
and running.
Once again.
Thank You.
DS 


0
DS
2/10/2008 8:50:00 PM
Reply:

Similar Artilces:

if A1=Null and B1=A1 why is result in B1=0 ??
if A1=Null and B1=A1 why is result in B1=0 ?? because of this I get wrong result for average calculations: average for (6,8,0) <> average for (6,8,null) Use =IF(A1="","",A1) -- HTH RP (remove nothere from the email address if mailing direct) "WGeorg" <WGeorg@discussions.microsoft.com> wrote in message news:09FCCF25-D0DC-4B1E-BFA1-CEF9A9327C46@microsoft.com... > if A1=Null and B1=A1 why is result in B1=0 ?? > because of this I get wrong result for average calculations: average for > (6,8,0) <> average for (6,8,null) Use this ...

Null Is Null
I've got a text box in a report that's not returning properly. Here's its Control Source: =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]! [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]! [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"") This returns #Error. It's definitely my IIf statement as it returns properly when removed however there are times when [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this subreport will not appear if no records exist. I suspect that this is the problem. How can I write this Control Source t...

Differnce between two dates
Hi I want to dermine how mnay days there is between cell c6 and g6 if g6 contains a value, if not then i want thee code to do nothjing. Mnay thanks You didn't mention which is the Start Date and which one is end date (i.e.) whether the start date is C6 Or G6. If the Start Date is C6 and the End Date is G6 then use the below formula =IF(G6="","",DATEDIF(C6,G6,"D")) If the Start Date is G6 and the End Date is C6 then use the below formula =IF(G6="","",DATEDIF(G6,C6,"D")) Remember to Click Yes, if this post h...

Graph displaying dates alphabetically on x axis, not by date
Hello! Title says it all - I want the x axis to show dates in DATE ORDER, not alphabetical order. I've read thru the forum, can't see how to fix it - your help will be appreciated. Kind Regards, Russell. p.s. Here is the graph Row Source, should there be an ORDER BY statement in here? SELECT (Format([Date Paid],"mmm"" '""yy")) AS Expr1, Sum([Tenant Payments]. Amount) AS SumOfAmount FROM [Tenant Payments] GROUP BY (Format([Date Paid], "mmm"" '""yy")), [Tenant Payments].[Date Paid], (Year([Date Paid]...

Dates
I am working on a spreadsheet that consists of a rolling 12 mont period. Once a month has passed how do I delete it and add a new mont to the 12 month period? Please help -- ~~ Message posted from http://www.ExcelForum.com You have to provide more info on how your data is laid out. Tell us what you have and tell us what you want to have when the month changes. HTH Otto "erodri02" <erodri02.y6nsy@excelforum-nospam.com> wrote in message news:erodri02.y6nsy@excelforum-nospam.com... > > I am working on a spreadsheet that consists of a rolling 12 month > period. Once...

Date Formula Help #2
I am tring to create a formuls that will allow me to do the following: In cell A1 have a start date, let's say 02/01/05 and in cell B 02/02/05 and so on all the way to the end of the month. But when I get to the end of the month the cell that would contain th date of 02/29/05 would be blank if it is not a leap year and if it is leap year then it would show the date of 02/29/05. Thanks a million for any help -- cummings ----------------------------------------------------------------------- cummingse's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2561 View ...

cannot save the date
I am at a loss why i cant save the todays date.On my form i have a control called LastUpdated.It is set to day/time and to a short date.I want to save the present date by the following: Me!LastUpdated = Now RunCommand acCmdSaveRecord DoCmd.Close acForm, Me.Name However the field lastUpdated stays blank and does not show the present date. What may be the reason ? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200708/1 On Wed, 29 Aug 2007 19:47:14 GMT, "peljo via AccessMonster.com" <u19312@uwe> wrote: >I am at a los...

Syntax for Is Not Null ?
When one of our forms is opened (Current event) I want a message box fired by certain conditions (that the Ref control does have a value, but the Country control is empty). I have tried various bits of code along the lines shown below, but keep getting runtime errors. Wot's wrong with the following, please? If [Me.Ref] is not null and [Me.Country] = "" Then MsgBox "Please enter the country!" Many thanks CW CW - Try this: If (not isnull([Me.Ref])) AND IsNull([Me.Country]) Then MsgBox "Please enter the country!" -- Daryl S ...

Unwanted automatic date format
I am doing some cut and past, and also some search and replace for numbers with a date-like format ("2-5", "2/5"), and Excell keeps interpreting these as dates, and inserts the date format. When I reformat the cell to text, the data is like "33679", I have lost the original data. I have repeatedly set the cell format to text before doing the search and replace, but for some reason, Excel still re-sets the format to Date. I have also tried reseting both the source and target spreadsheets to the text format before I cut and past. In both cases the problem...

IF the date in A1 is a holiday, cell B1 should say "HOLIDAY"
Here's what I want to do but I'm too dumb to it. Usually I Google before asking here but my Internet is down though NGs and email work. PART 1 In cell A1 we enter a date, say, 07/22/04. If the date in A1 is a holiday I want cell B1 to show the word HOLIDAY. If A1 is not a holiday, B1 should be blank. I will list the holiday dates in cells D1 to D10. PART 2 Part 2 is the same as 1 but instead of the word HOLIDAY appearing the name of the holiday, NEWS YEARS DAY, will appear. I will list the holiday names in cells E1 to E10 next to the dates in column D. Hi part 1: =IF(ISNUMBER(MATC...

limit Criteria from a date field
I have a field that performs a calcualtion based on a date: Age: ([DATE]-[TR_DATE]). I need to add to the criteria >50 But the error says Arithmatic overflow error, guess because it was a date value to begin with. I tried creating anothe field from that one: Age at Panel: ([Age]>50) which give me a -1 or 0 but I still cannot make the criteria = -1 or 0. I tried another query but it just carries over the same issues. How do I allow only >50 from this calculation???? Dan @BCBS <DanBCBS@discussions.microsoft.com> wrote: >I have a field that performs a calcualtion base...

Function to set Date
Hi, I would like to know how to set below equation:- to set the 1-week (i.e Monday) prior to a specific date: e.g. 2009/11/24, i would like to have the output = 2009/11/16 e.g. 2009/12/31, output = 2009/12/21 -- J Hi, Try this. Cell B16 holds the specific date =B16-7-CHOOSE(WEEKDAY(B16-7,2),0,1,2,3,4,5,6) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "J" <J@discussions.microsoft.com> wrote in message news:A97DB69C-680E-4A4E-95C9-F731BE3BE9D4@microsoft.com... > Hi, > > I would like to know how to set below eq...

When is a Null not a Null?
I have a form which is used for adding and entering data depending on where it is called from. When called in add mode (acFormAdd) a blank form is openned. When the cursor enters the text box [Name] on the form field I am trying test whether it is empty so that a search form can be openned if a new name is to be added. (I don't want the search form to open in cases when the form already has data in and I am editing rather than adding data). The form is based on a query and the data displayed in [Name] is the result of a calculated field in the query which concatenates firstname and surn...

Sum Where Date is less than certain date
I might be getting too technical for my level, but here is what I am trying to do. I want to sum amounts from a query that is not the source for my form. I have the sum working, but I want to break it down. I am trying to sum the numbers that are less than the date in the date field of my form. Here is what I have so far, courtesy of help I received in this group: =IIf([Car] Is Not Null,IIf([Car]="1",DSum("[Miles on Previous Tank]","AutosGasStats1")/DSum("[Gallons Pumped]","AutosGasStats1"),DSum("[Miles on Previous Tank]","A...

What is the Null character?
What is the character sequence that Excel uses to represent an empty cell? I am using a formula and when it returns false, I would like for it to represent the cell as if it was empty. I have tried using "" as an empty cell, but excel recognizes that this is a value, not an empty cell. You can't do what you want to do. A formula always returns *something*. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Quinn Ramsey" <Quinn Ramsey@discussions.microsoft.com> wrote in message news:D67F6311-0CCA-48B5-A...

How can I get an age using todays date and date of birth?
I'm using a spreadsheet where I need ages listed. Is there a formula that can be used to compute the age if I use todays date and date of birth? So far I am not having any luck. Thanks for any help you may provide. I am using Excel 2000. assuming the birthdate is in cell A1 =YEAR(NOW())-YEAR(A1) "tjw1313" wrote: > I'm using a spreadsheet where I need ages listed. Is there a formula that > can be used to compute the age if I use todays date and date of birth? So > far I am not having any luck. Thanks for any help you may provide. I am > using Excel...

Date formula #12
Hello All: I have a spreadsheet that I need to have the dates for the month. On this application I needed the First of the month in one cell and the last day of the month listed in another. I inserted a calendar that allows me to put the selected date into the active cell. (Formatted ddd mmmm dd yyyy) The I use the formula =DATE(YEAR(C6),MONTH(C6)+1,DAY(C6)-1) to enter the last day of the month into the appropriate cell. (Formatted mmmm dd yy) The problem I am having is that this formula works on most of the dates in a month but gives me a #NUM! Error when it is asked to give me the l...

Charting null values
I have a chart that is plotting null values. I tried the formula =if(C25="",N/A(),Sum(C22:C25)). I've also deleted the formula to create a completely null cell. I've been to Tools, Options, Chart and selected Plot empty cells as Not plotted, zero and Interpollated As well as turning Plot Visible Cells only (turned it on and off) Nothing seems to make a difference. Any suggestions? Stephanie, what kind of chart are you using? Can you give an example of how your data table looks like? br, Henk "StephanieH" wrote: > I have a chart that is plotting ...

text is being interpreted as a date
I have the following value which I split into 4 columns using Text T Columns (within a vba routine) : 02-9508~8203.20.6060~646~5. (delimited on the ~ ) the problem is that the first column of data gets automatically (an incorrectly) recognized as a date (Feb-08). If I format it afterwards, it turns it into 2778797. That value is supposed to be text (02-9508). does anyone know how I can keep excel from incorrectly misinterprettin the format of the data ??? :confused -- Message posted from http://www.ExcelForum.com format your columns as "Text" before you do your text to co...

PrimaryContactID NULL
Hello, i have discovered a strange behavior of CRM regarding the relationship between accounts and contacts: Some of my accounts show up a referenced contact but do not have a PrimaryContactID GUID in the databasefield. Both where converted from a lead which is refrenced with the correct GUID in the OriginatingLeadID Field in the database? Any idea how this can happen? Regards Sebastian When you convert a lead into an account/contact the contact references the account throught the parent customer field. The account does not reference the contact (this would be the primary contact fi...

Formatting a text box for date entry only
Is there a way to format a text box on a user form to only allow date entries ie: mm/dd/yy or mm/dd/yyyy? Not really, but you can ensure it is a date, like so Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim fValid As Boolean fValid = False On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then With Target If IsNumeric(.Value) Then If IsDate(.Value) Then If Year(.Value) >= 1970 And Year(.Value) <= 2999 Then ...

NULL values
Working with a downloaded table form a Lotus Notes contact manager. Now in access call tblclients. I query certain info. to update the records. BUT...'Is Null' does not work. It comes back empty and there are definitely empty contents in the fields I chose. I know how to do this as it works in my other tables on other databases. The only difference I can see in this table as opposed to tables created directly in Access is the Field Design property called Unicode Compression saying No instead of the default Yes. I changed the Unicode to Yes in case this had something to do with it and ...

Date anomaly
My spreadsheet has a couple of date columns. Some of the rows would have no date in it. However, after running the macros - which uses these cell value as "blank", the date of "1/1/1900" appearred in those blank cells and I could not make it disappear. Do you have any suggestion to make that date disappear? Since I am using VBA, I would prefer some command scripts that would turn those cells "off". Thanks in advance. DP DP Your code must be returning a 1 somehow which is converted to that date (Day 1 in xl = 1/1/1900, you must explicitly test for th...

Using CORREL with arrays containing null values
XL Gurus... I'm using the CORREL function, but one of the arrays I'm comparing has null values (entered as #N/A), so my result is #N/A. How can I modify my formula to correlate all the points in the 2 arrays, apart from the null values and their corresponding entries in the other array? You might incorporate an IF function, along the lines of IF(ISNUMBER(your array function here),your array function here,0) It seems redundant, but it evaluates the array function to determine if it is numeric. ...

Hide Sub Report(s) when records are null
I am running Access 2007 sp2 MSO. I have a report that has three (3) sub reports. There are times when not all three sub reports have values to display. Is there a way to code the given report to not be visible if there are no records to report? The second part of this question would be if there is a way to make the report invisible is it also possible to code so that the space where the report would normally reside would not be taken with a blank space (the foot print of where the report normally resides)? -- Bruce ...