Syntax error in date in query expression '##'

  • Thread starter Thread starter Guest
  • Start date Start date
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] & "#," & _
");"
 
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 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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top