Dealing with Null Dates in an Append Query 
Author Message
 Dealing with Null Dates in an Append Query

Group:

I have the following Append Query that works fine when all of the date
fields have date values; however, when a date is left blank, the query
enters 12/30/1899. I want the query to enter a blank into the field when the
BegDate and/or EndDate variables are Null (blank). How do I do this?

db.Execute "INSERT INTO [MasterData-1] ( Name, Beginning, Ending ) IN '" &
BackupPathName & "' SELECT '" & SelectedName & "-1' AS Name, #" & BegDate &
"# AS Beginning, #" & EndDate & "# AS Ending;"

Thanks,

BobV



Wed, 08 Dec 2004 11:19:27 GMT  
 Dealing with Null Dates in an Append Query
Test for Null when building your string:

strSQL = "INSERT ... AS Name, "
If Not IsNull(Me.BegDate) Then
    strSQL = strSQL & Format(Me.BegDate, "\#mm\/dd\/yyyy\#") & " AS
Beginning, "
End If
'etc.

Note: If the field has a default value that you wish to override, your
string can explicitly insert:
    Null As Beginning,

--
Allen Browne - Microsoft MVP (Most Valuable Professional)
Perth, Western Australia.
Allen Browne's Database And Training.
Tips for MS Access users: http://users.bigpond.net.au/abrowne1
Reply to the newsgroup. (Email address has spurious "_SpamTrap")


Quote:
> Group:

> I have the following Append Query that works fine when all of the date
> fields have date values; however, when a date is left blank, the query
> enters 12/30/1899. I want the query to enter a blank into the field when
the
> BegDate and/or EndDate variables are Null (blank). How do I do this?

> db.Execute "INSERT INTO [MasterData-1] ( Name, Beginning, Ending ) IN '"
&
> BackupPathName & "' SELECT '" & SelectedName & "-1' AS Name, #" & BegDate
&
> "# AS Beginning, #" & EndDate & "# AS Ending;"

> Thanks,

> BobV



Wed, 08 Dec 2004 13:38:29 GMT  
 Dealing with Null Dates in an Append Query
There's also Joe Foster's approach, as suggested as
http://www.mvps.org/access/datetime/date005.htm at Dev Ashish's "The Access
Web"

 strSQL = "INSERT ... AS Name, "
 strSQL = strSQL & Format(Me.BegDate, "\#mm\/dd\/yyyy\#;;;\N\u\l\l") & " AS
Beginning, "

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


Quote:
> Test for Null when building your string:

> strSQL = "INSERT ... AS Name, "
> If Not IsNull(Me.BegDate) Then
>     strSQL = strSQL & Format(Me.BegDate, "\#mm\/dd\/yyyy\#") & " AS
> Beginning, "
> End If
> 'etc.

> Note: If the field has a default value that you wish to override, your
> string can explicitly insert:
>     Null As Beginning,

> --
> Allen Browne - Microsoft MVP (Most Valuable Professional)
> Perth, Western Australia.
> Allen Browne's Database And Training.
> Tips for MS Access users: http://users.bigpond.net.au/abrowne1
> Reply to the newsgroup. (Email address has spurious "_SpamTrap")



> > Group:

> > I have the following Append Query that works fine when all of the date
> > fields have date values; however, when a date is left blank, the query
> > enters 12/30/1899. I want the query to enter a blank into the field when
> the
> > BegDate and/or EndDate variables are Null (blank). How do I do this?

> > db.Execute "INSERT INTO [MasterData-1] ( Name, Beginning, Ending ) IN '"
> &
> > BackupPathName & "' SELECT '" & SelectedName & "-1' AS Name, #" &
BegDate
> &
> > "# AS Beginning, #" & EndDate & "# AS Ending;"

> > Thanks,

> > BobV



Wed, 08 Dec 2004 18:45:26 GMT  
 Dealing with Null Dates in an Append Query
Thanks to both Doug Steele and Allen Browne for their help. I greatly
appreciate it.

BobV



Quote:
> There's also Joe Foster's approach, as suggested as
> http://www.mvps.org/access/datetime/date005.htm at Dev Ashish's "The
Access
> Web"

>  strSQL = "INSERT ... AS Name, "
>  strSQL = strSQL & Format(Me.BegDate, "\#mm\/dd\/yyyy\#;;;\N\u\l\l") & "
AS
> Beginning, "

> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele



> > Test for Null when building your string:

> > strSQL = "INSERT ... AS Name, "
> > If Not IsNull(Me.BegDate) Then
> >     strSQL = strSQL & Format(Me.BegDate, "\#mm\/dd\/yyyy\#") & " AS
> > Beginning, "
> > End If
> > 'etc.

> > Note: If the field has a default value that you wish to override, your
> > string can explicitly insert:
> >     Null As Beginning,

> > --
> > Allen Browne - Microsoft MVP (Most Valuable Professional)
> > Perth, Western Australia.
> > Allen Browne's Database And Training.
> > Tips for MS Access users: http://users.bigpond.net.au/abrowne1
> > Reply to the newsgroup. (Email address has spurious "_SpamTrap")



> > > Group:

> > > I have the following Append Query that works fine when all of the date
> > > fields have date values; however, when a date is left blank, the query
> > > enters 12/30/1899. I want the query to enter a blank into the field
when
> > the
> > > BegDate and/or EndDate variables are Null (blank). How do I do this?

> > > db.Execute "INSERT INTO [MasterData-1] ( Name, Beginning, Ending ) IN
'"
> > &
> > > BackupPathName & "' SELECT '" & SelectedName & "-1' AS Name, #" &
> BegDate
> > &
> > > "# AS Beginning, #" & EndDate & "# AS Ending;"

> > > Thanks,

> > > BobV



Wed, 08 Dec 2004 22:29:01 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Check to See if a Date exist before an append Query ran

2. Append query with date parameters

3. How to query on a null date field

4. Q: SQL query with NULL dates

5. How to query on a null date field

6. Append query that appends to and updates an archive table

7. Make Table Query vs Append Query

8. Problem dealing with null value

9. Function to deal with nulls

10. dealing with NULL values in a SQL Table.

11. Dealing with Null efficiently in ASP...

12. Make-Table queries and Append Queries

 

 
Powered by phpBB® Forum Software