Syntax error in date in query expression '##'

G

Guest

Hi folks,

The following code works well until it finds a record where the ContractDate
field is blank. I get the above error when there is an empty date field.
Can anyone give me some guidance here?

Thanks!

TC2

-------------------------------------------------------------------------

StrSQL = "INSERT INTO [Contract Info] (ContractInfoLoginName,
ContractInfoDateModified, ContractInfoTimeModified, ContractNumber,
CustomerName, OriginalContractNegotiator, ContractDate)

StrSQL = StrSQL & _
"VALUES (" & _
"'" & Forms![Contract Info Edit Form]![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & time & "#," & _
"'" & Forms![Contract Info Edit Form]![ContractNumber] & "'," & _
"'" & Forms![Contract Info Edit Form]![CustomerName] & "'," & _
"'" & Forms![Contract Info Edit Form]![OriginalContractNegotiator] &
"'," & _
"#" & Forms![Contract Info Edit Form]![ContractDate] & "#," & _
");"
 
G

Guest

Check the value of the contract date field before executing your insert.

How is this code executed, in an event procedure?
Is it in the [Contract Info Edit Form] module?

Steve
 
G

Guest

Hi SteveM,

Yes, the contract date field is gathered in the form. A "submit" button on
the form initiates the insert query through a click event. For testing I've
tested for null and inserted the current date (until I can figure out how to
deal with it right). Is there syntax that I can use to deal with null or an
actual date value?


--
TC2


SteveM said:
Check the value of the contract date field before executing your insert.

How is this code executed, in an event procedure?
Is it in the [Contract Info Edit Form] module?

Steve

TC2 said:
Hi folks,

The following code works well until it finds a record where the ContractDate
field is blank. I get the above error when there is an empty date field.
Can anyone give me some guidance here?

Thanks!

TC2

-------------------------------------------------------------------------

StrSQL = "INSERT INTO [Contract Info] (ContractInfoLoginName,
ContractInfoDateModified, ContractInfoTimeModified, ContractNumber,
CustomerName, OriginalContractNegotiator, ContractDate)

StrSQL = StrSQL & _
"VALUES (" & _
"'" & Forms![Contract Info Edit Form]![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & time & "#," & _
"'" & Forms![Contract Info Edit Form]![ContractNumber] & "'," & _
"'" & Forms![Contract Info Edit Form]![CustomerName] & "'," & _
"'" & Forms![Contract Info Edit Form]![OriginalContractNegotiator] &
"'," & _
"#" & Forms![Contract Info Edit Form]![ContractDate] & "#," & _
");"
Click to expand...
Click to expand...
 
G

Guest

So, you want to enter the current date if it is Null?

Try this:
StrSQL = StrSQL & _
"VALUES (" & _
"'" & Me![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & Time & "#," & _
"'" & Me![ContractNumber] & "'," & _
"'" & Me![CustomerName] & "'," & _
"'" & Me![OriginalContractNegotiator] &
"'," & _
"#" & Nz(Me![ContractDate], Date) & "#," & _
");"

Steve

TC2 said:
Hi SteveM,

Yes, the contract date field is gathered in the form. A "submit" button on
the form initiates the insert query through a click event. For testing I've
tested for null and inserted the current date (until I can figure out how to
deal with it right). Is there syntax that I can use to deal with null or an
actual date value?


--
TC2


SteveM said:
Check the value of the contract date field before executing your insert.

How is this code executed, in an event procedure?
Is it in the [Contract Info Edit Form] module?

Steve

TC2 said:
Hi folks,

The following code works well until it finds a record where the ContractDate
field is blank. I get the above error when there is an empty date field.
Can anyone give me some guidance here?

Thanks!

TC2

-------------------------------------------------------------------------

StrSQL = "INSERT INTO [Contract Info] (ContractInfoLoginName,
ContractInfoDateModified, ContractInfoTimeModified, ContractNumber,
CustomerName, OriginalContractNegotiator, ContractDate)

StrSQL = StrSQL & _
"VALUES (" & _
"'" & Forms![Contract Info Edit Form]![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & time & "#," & _
"'" & Forms![Contract Info Edit Form]![ContractNumber] & "'," & _
"'" & Forms![Contract Info Edit Form]![CustomerName] & "'," & _
"'" & Forms![Contract Info Edit Form]![OriginalContractNegotiator] &
"'," & _
"#" & Forms![Contract Info Edit Form]![ContractDate] & "#," & _
");"
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Again,

No. Sorry Steve. I've already coded it to use the current date if the
value is null, just to move testing along. However, I would like to store
"null" if the user has not entered a contract date. Does that make sense?
--
TC2


SteveM said:
So, you want to enter the current date if it is Null?

Try this:
StrSQL = StrSQL & _
"VALUES (" & _
"'" & Me![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & Time & "#," & _
"'" & Me![ContractNumber] & "'," & _
"'" & Me![CustomerName] & "'," & _
"'" & Me![OriginalContractNegotiator] &
"'," & _
"#" & Nz(Me![ContractDate], Date) & "#," & _
");"

Steve

TC2 said:
Hi SteveM,

Yes, the contract date field is gathered in the form. A "submit" button on
the form initiates the insert query through a click event. For testing I've
tested for null and inserted the current date (until I can figure out how to
deal with it right). Is there syntax that I can use to deal with null or an
actual date value?


--
TC2


SteveM said:
Check the value of the contract date field before executing your insert.

How is this code executed, in an event procedure?
Is it in the [Contract Info Edit Form] module?

Steve

:

Hi folks,

The following code works well until it finds a record where the ContractDate
field is blank. I get the above error when there is an empty date field.
Can anyone give me some guidance here?

Thanks!

TC2

-------------------------------------------------------------------------

StrSQL = "INSERT INTO [Contract Info] (ContractInfoLoginName,
ContractInfoDateModified, ContractInfoTimeModified, ContractNumber,
CustomerName, OriginalContractNegotiator, ContractDate)

StrSQL = StrSQL & _
"VALUES (" & _
"'" & Forms![Contract Info Edit Form]![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & time & "#," & _
"'" & Forms![Contract Info Edit Form]![ContractNumber] & "'," & _
"'" & Forms![Contract Info Edit Form]![CustomerName] & "'," & _
"'" & Forms![Contract Info Edit Form]![OriginalContractNegotiator] &
"'," & _
"#" & Forms![Contract Info Edit Form]![ContractDate] & "#," & _
");"
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ok, try this:
StrSQL = StrSQL & _
"VALUES (" & _
"'" & Me![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & Time & "#," & _
"'" & Me![ContractNumber] & "'," & _
"'" & Me![CustomerName] & "'," & _
"'" & Me![OriginalContractNegotiator] & "',"

If Nz(Me!ContractDate,"") = "" Then
StrSQL = StrSQL & "Null);"
Else
StrSQL = StrSQL & "#" & Me![ContractDate] & "#);"
End If

Steve

TC2 said:
Hi Again,

No. Sorry Steve. I've already coded it to use the current date if the
value is null, just to move testing along. However, I would like to store
"null" if the user has not entered a contract date. Does that make sense?
--
TC2


SteveM said:
So, you want to enter the current date if it is Null?

Try this:
StrSQL = StrSQL & _
"VALUES (" & _
"'" & Me![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & Time & "#," & _
"'" & Me![ContractNumber] & "'," & _
"'" & Me![CustomerName] & "'," & _
"'" & Me![OriginalContractNegotiator] &
"'," & _
"#" & Nz(Me![ContractDate], Date) & "#," & _
");"

Steve

TC2 said:
Hi SteveM,

Yes, the contract date field is gathered in the form. A "submit" button on
the form initiates the insert query through a click event. For testing I've
tested for null and inserted the current date (until I can figure out how to
deal with it right). Is there syntax that I can use to deal with null or an
actual date value?


--
TC2


:

Check the value of the contract date field before executing your insert.

How is this code executed, in an event procedure?
Is it in the [Contract Info Edit Form] module?

Steve

:

Hi folks,

The following code works well until it finds a record where the ContractDate
field is blank. I get the above error when there is an empty date field.
Can anyone give me some guidance here?

Thanks!

TC2

-------------------------------------------------------------------------

StrSQL = "INSERT INTO [Contract Info] (ContractInfoLoginName,
ContractInfoDateModified, ContractInfoTimeModified, ContractNumber,
CustomerName, OriginalContractNegotiator, ContractDate)

StrSQL = StrSQL & _
"VALUES (" & _
"'" & Forms![Contract Info Edit Form]![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & time & "#," & _
"'" & Forms![Contract Info Edit Form]![ContractNumber] & "'," & _
"'" & Forms![Contract Info Edit Form]![CustomerName] & "'," & _
"'" & Forms![Contract Info Edit Form]![OriginalContractNegotiator] &
"'," & _
"#" & Forms![Contract Info Edit Form]![ContractDate] & "#," & _
");"
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hey Steve,

Thanks for your solution. I'll give it a try tomorrow morning and let you
know how it goes.

Thanks Again!

TC2


SteveM said:
Ok, try this:
StrSQL = StrSQL & _
"VALUES (" & _
"'" & Me![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & Time & "#," & _
"'" & Me![ContractNumber] & "'," & _
"'" & Me![CustomerName] & "'," & _
"'" & Me![OriginalContractNegotiator] & "',"

If Nz(Me!ContractDate,"") = "" Then
StrSQL = StrSQL & "Null);"
Else
StrSQL = StrSQL & "#" & Me![ContractDate] & "#);"
End If

Steve

TC2 said:
Hi Again,

No. Sorry Steve. I've already coded it to use the current date if the
value is null, just to move testing along. However, I would like to store
"null" if the user has not entered a contract date. Does that make sense?
--
TC2


SteveM said:
So, you want to enter the current date if it is Null?

Try this:
StrSQL = StrSQL & _
"VALUES (" & _
"'" & Me![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & Time & "#," & _
"'" & Me![ContractNumber] & "'," & _
"'" & Me![CustomerName] & "'," & _
"'" & Me![OriginalContractNegotiator] &
"'," & _
"#" & Nz(Me![ContractDate], Date) & "#," & _
");"

Steve

:

Hi SteveM,

Yes, the contract date field is gathered in the form. A "submit" button on
the form initiates the insert query through a click event. For testing I've
tested for null and inserted the current date (until I can figure out how to
deal with it right). Is there syntax that I can use to deal with null or an
actual date value?


--
TC2


:

Check the value of the contract date field before executing your insert.

How is this code executed, in an event procedure?
Is it in the [Contract Info Edit Form] module?

Steve

:

Hi folks,

The following code works well until it finds a record where the ContractDate
field is blank. I get the above error when there is an empty date field.
Can anyone give me some guidance here?

Thanks!

TC2

-------------------------------------------------------------------------

StrSQL = "INSERT INTO [Contract Info] (ContractInfoLoginName,
ContractInfoDateModified, ContractInfoTimeModified, ContractNumber,
CustomerName, OriginalContractNegotiator, ContractDate)

StrSQL = StrSQL & _
"VALUES (" & _
"'" & Forms![Contract Info Edit Form]![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & time & "#," & _
"'" & Forms![Contract Info Edit Form]![ContractNumber] & "'," & _
"'" & Forms![Contract Info Edit Form]![CustomerName] & "'," & _
"'" & Forms![Contract Info Edit Form]![OriginalContractNegotiator] &
"'," & _
"#" & Forms![Contract Info Edit Form]![ContractDate] & "#," & _
");"
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hey Steve,

That worked perfectly. Thanks again for your time and expertise!

TC2


TC2 said:
Hey Steve,

Thanks for your solution. I'll give it a try tomorrow morning and let you
know how it goes.

Thanks Again!

TC2


SteveM said:
Ok, try this:
StrSQL = StrSQL & _
"VALUES (" & _
"'" & Me![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & Time & "#," & _
"'" & Me![ContractNumber] & "'," & _
"'" & Me![CustomerName] & "'," & _
"'" & Me![OriginalContractNegotiator] & "',"

If Nz(Me!ContractDate,"") = "" Then
StrSQL = StrSQL & "Null);"
Else
StrSQL = StrSQL & "#" & Me![ContractDate] & "#);"
End If

Steve

TC2 said:
Hi Again,

No. Sorry Steve. I've already coded it to use the current date if the
value is null, just to move testing along. However, I would like to store
"null" if the user has not entered a contract date. Does that make sense?
--
TC2


:

So, you want to enter the current date if it is Null?

Try this:
StrSQL = StrSQL & _
"VALUES (" & _
"'" & Me![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & Time & "#," & _
"'" & Me![ContractNumber] & "'," & _
"'" & Me![CustomerName] & "'," & _
"'" & Me![OriginalContractNegotiator] &
"'," & _
"#" & Nz(Me![ContractDate], Date) & "#," & _
");"

Steve

:

Hi SteveM,

Yes, the contract date field is gathered in the form. A "submit" button on
the form initiates the insert query through a click event. For testing I've
tested for null and inserted the current date (until I can figure out how to
deal with it right). Is there syntax that I can use to deal with null or an
actual date value?


--
TC2


:

Check the value of the contract date field before executing your insert.

How is this code executed, in an event procedure?
Is it in the [Contract Info Edit Form] module?

Steve

:

Hi folks,

The following code works well until it finds a record where the ContractDate
field is blank. I get the above error when there is an empty date field.
Can anyone give me some guidance here?

Thanks!

TC2

-------------------------------------------------------------------------

StrSQL = "INSERT INTO [Contract Info] (ContractInfoLoginName,
ContractInfoDateModified, ContractInfoTimeModified, ContractNumber,
CustomerName, OriginalContractNegotiator, ContractDate)

StrSQL = StrSQL & _
"VALUES (" & _
"'" & Forms![Contract Info Edit Form]![ContractInfoLoginName] & "'," & _
"#" & Date & "#," & _
"#" & time & "#," & _
"'" & Forms![Contract Info Edit Form]![ContractNumber] & "'," & _
"'" & Forms![Contract Info Edit Form]![CustomerName] & "'," & _
"'" & Forms![Contract Info Edit Form]![OriginalContractNegotiator] &
"'," & _
"#" & Forms![Contract Info Edit Form]![ContractDate] & "#," & _
");"
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

append query for associate entity 2
Access Access Eliminate Blank Query Results 1
Repost: Requery To Update Issues 1
Need to chg queried table 13
Database Design/Change Help Requested 2
Syntax error (comma) in query - ????????????????????????? 1
Syntax error in query expression 2
Run-Time error '2424': 1

Top