SQL VALUE list

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

In the course of inserting a new record into a table:

CurrentDb.Execute NewWorksSQL, dbFailOnError

I want to add a new field name and value as the SQL
string is constructed. The field is defined as ShortDate
in the target table and initially wouldn't have a value.
I Dim'd a temporary variable in my code-sheet to use
as the table's initial field value, but SQL continuously
gives errors for everything I've tried.

Given that the table field is of type "ShortDate", what do I
use in the SQL VALUE list when the table field is empty?

Thanks,
Bill
 
Bill,

I'm confused. You state that you want to add a new field name in your table,
but then you say the it is already defined in the target table. How is this
possible? Are you instead wanting to rename the existing field name = to the
value you'd like to insert? If so, why?

Brian
 
Brian,
Probably gave too much information. The ShortDate field
already exists in a table. All I want to do is augment an
existing SQL statement "NewWorksSQL" to include
the name of the field and its initial value. What I don't know
how to do is specify its initial value of "nothing" or "empty"
or "null" or whatever that goes in the "Value list" portion of
"NewWorksSQL".
Bill
 
I think I understand. You're performing an append query with vba. You need
to convert the Null date. The following example worked in my test. It set
the date to 00:00:00

Dim NewWorksSQL as String

NewWorksSQL = "INSERT INTO [yourtable] " & _
"(SomeField, ShortDate) " & _
"Values ('" & SomeText & "', " & Nz(SomeDate,0) & ")"

CurrentDb.Execute NewWorksSQL, dbFailOnError

Is this what you're after?

Brian
 
A thought just occurred to me. Just because you're entering a new record,
you don't have to try inserting a null date. Just leave it out of the INSERT
statement. In your table, that field in that record will remain blank.
Otherwise, your date field will end up looking like 00:00:00 instead of
mm/dd/yy. Good thing I tried this on a dummy table!

HTH,
Brian


Brian Bastl said:
I think I understand. You're performing an append query with vba. You need
to convert the Null date. The following example worked in my test. It set
the date to 00:00:00

Dim NewWorksSQL as String

NewWorksSQL = "INSERT INTO [yourtable] " & _
"(SomeField, ShortDate) " & _
"Values ('" & SomeText & "', " & Nz(SomeDate,0) & ")"

CurrentDb.Execute NewWorksSQL, dbFailOnError

Is this what you're after?

Brian


Bill said:
Brian,
Probably gave too much information. The ShortDate field
already exists in a table. All I want to do is augment an
existing SQL statement "NewWorksSQL" to include
the name of the field and its initial value. What I don't know
how to do is specify its initial value of "nothing" or "empty"
or "null" or whatever that goes in the "Value list" portion of
"NewWorksSQL".
Bill
Click to expand...
Click to expand...
 
Brian,
I had already decided that I would test the current value
of the questionable field and set the SQL statement
with or without its inclusion depending on whether it
had a value set or not.
Thanks,
Bill


Brian Bastl said:
A thought just occurred to me. Just because you're entering a new record,
you don't have to try inserting a null date. Just leave it out of the
INSERT
statement. In your table, that field in that record will remain blank.
Otherwise, your date field will end up looking like 00:00:00 instead of
mm/dd/yy. Good thing I tried this on a dummy table!

HTH,
Brian


Brian Bastl said:
I think I understand. You're performing an append query with vba. You
need
to convert the Null date. The following example worked in my test. It set
the date to 00:00:00

Dim NewWorksSQL as String

NewWorksSQL = "INSERT INTO [yourtable] " & _
"(SomeField, ShortDate) " & _
"Values ('" & SomeText & "', " & Nz(SomeDate,0) & ")"

CurrentDb.Execute NewWorksSQL, dbFailOnError

Is this what you're after?

Brian


Bill said:
Brian,
Probably gave too much information. The ShortDate field
already exists in a table. All I want to do is augment an
existing SQL statement "NewWorksSQL" to include
the name of the field and its initial value. What I don't know
how to do is specify its initial value of "nothing" or "empty"
or "null" or whatever that goes in the "Value list" portion of
"NewWorksSQL".
Bill

Bill,

I'm confused. You state that you want to add a new field name in your
table,
but then you say the it is already defined in the target table. How
is
this
possible? Are you instead wanting to rename the existing field name = to
the
value you'd like to insert? If so, why?

Brian

In the course of inserting a new record into a table:

CurrentDb.Execute NewWorksSQL, dbFailOnError

I want to add a new field name and value as the SQL
string is constructed. The field is defined as ShortDate
in the target table and initially wouldn't have a value.
I Dim'd a temporary variable in my code-sheet to use
as the table's initial field value, but SQL continuously
gives errors for everything I've tried.

Given that the table field is of type "ShortDate", what do I
use in the SQL VALUE list when the table field is empty?

Thanks,
Bill
Click to expand...
Click to expand...
Click to expand...
 
Back
Top