#Error in Query

[Guest]

Hi,

I am using the following code in a query and i am getting #Error when i run
the query and because of this i am not able to export to Excel. Please help
me in this.

1st Doc DateTime: CDate([1stDOC Receipt date])++TimeValue((Left$([1stDOC
Receipt time],2))+":"+(Right$([1stDOC Receipt time],2)))

 

[Duane Hookom]

You have two adjacent "+" in your expression.

 

[John Spencer]

What you posted had two pluses "++" together which is going to cause an error.

Beyond that it is difficult to guess if other problems exist since we have no
idea of the contents of the field [1stDOC Receipt time]
Also, you will get errors if [1stDOC Receipt date] is null (or invalid as a
date). And the same for the string you are building to get the time - if it is
null or not a valid time it will generate errors. AND is the time in the time
field stored in 24 hour format? If not, all your times will be in the first
half of the day.

1st Doc DateTime: CDate([1stDOC Receipt date])+
TimeValue((Left$([1stDOC Receipt time],2))+":"+(Right$([1stDOC Receipt time],2)))

 

[Guest]

HI John,

Thak you so much for your answer. We have linked the data to a .csv file.
{1st Doc Rec] is a text field.. we have converted that in to a date field
using the CDate() function. Now where ever date exists it converts ... but
where there is no date it show #ERROR. Due to which we cannot link it to an
Excel Sheet.

Can you device a solution to this ... or can you explain how we can use
Iserror function.

Thank you in advance for your help.
--
Varun Nair

What you posted had two pluses "++" together which is going to cause an error.

Beyond that it is difficult to guess if other problems exist since we have no
idea of the contents of the field [1stDOC Receipt time]
Also, you will get errors if [1stDOC Receipt date] is null (or invalid as a
date). And the same for the string you are building to get the time - if it is
null or not a valid time it will generate errors. AND is the time in the time
field stored in 24 hour format? If not, all your times will be in the first
half of the day.

1st Doc DateTime: CDate([1stDOC Receipt date])+
TimeValue((Left$([1stDOC Receipt time],2))+":"+(Right$([1stDOC Receipt time],2)))

Hi,

I am using the following code in a query and i am getting #Error when i run
the query and because of this i am not able to export to Excel. Please help
me in this.

1st Doc DateTime: CDate([1stDOC Receipt date])++TimeValue((Left$([1stDOC
Receipt time],2))+":"+(Right$([1stDOC Receipt time],2)))

 

[John Spencer]

Try the following expression

1st Doc DateTime:
IIF(IsDate([1stDoc Receipt Date],
CDate([1stDOC Receipt date])&
TimeValue((Left$([1stDOC Receipt time],2))&":"&(Right$([1stDOC Receipt time],2))))

HI John,

Thak you so much for your answer. We have linked the data to a .csv file.
{1st Doc Rec] is a text field.. we have converted that in to a date field
using the CDate() function. Now where ever date exists it converts ... but
where there is no date it show #ERROR. Due to which we cannot link it to an
Excel Sheet.

Can you device a solution to this ... or can you explain how we can use
Iserror function.

Thank you in advance for your help.
--
Varun Nair

What you posted had two pluses "++" together which is going to cause an error.

Beyond that it is difficult to guess if other problems exist since we have no
idea of the contents of the field [1stDOC Receipt time]
Also, you will get errors if [1stDOC Receipt date] is null (or invalid as a
date). And the same for the string you are building to get the time - if it is
null or not a valid time it will generate errors. AND is the time in the time
field stored in 24 hour format? If not, all your times will be in the first
half of the day.

1st Doc DateTime: CDate([1stDOC Receipt date])+
TimeValue((Left$([1stDOC Receipt time],2))+":"+(Right$([1stDOC Receipt time],2)))

Hi,

I am using the following code in a query and i am getting #Error when i run
the query and because of this i am not able to export to Excel. Please help
me in this.

1st Doc DateTime: CDate([1stDOC Receipt date])++TimeValue((Left$([1stDOC
Receipt time],2))+":"+(Right$([1stDOC Receipt time],2)))

 

[Guest]

Hi John..

Awesome! It did wonders! Thanx for your support you are a star!
--
Varun Nair

Try the following expression

1st Doc DateTime:
IIF(IsDate([1stDoc Receipt Date],
CDate([1stDOC Receipt date])&
TimeValue((Left$([1stDOC Receipt time],2))&":"&(Right$([1stDOC Receipt time],2))))

HI John,

Thak you so much for your answer. We have linked the data to a .csv file.
{1st Doc Rec] is a text field.. we have converted that in to a date field
using the CDate() function. Now where ever date exists it converts ... but
where there is no date it show #ERROR. Due to which we cannot link it to an
Excel Sheet.

Can you device a solution to this ... or can you explain how we can use
Iserror function.

Thank you in advance for your help.
--
Varun Nair

What you posted had two pluses "++" together which is going to cause an error.

Beyond that it is difficult to guess if other problems exist since we have no
idea of the contents of the field [1stDOC Receipt time]
Also, you will get errors if [1stDOC Receipt date] is null (or invalid as a
date). And the same for the string you are building to get the time - if it is
null or not a valid time it will generate errors. AND is the time in the time
field stored in 24 hour format? If not, all your times will be in the first
half of the day.

1st Doc DateTime: CDate([1stDOC Receipt date])+
TimeValue((Left$([1stDOC Receipt time],2))+":"+(Right$([1stDOC Receipt time],2)))

Varun Nair wrote:

Hi,

I am using the following code in a query and i am getting #Error when i run
the query and because of this i am not able to export to Excel. Please help
me in this.

1st Doc DateTime: CDate([1stDOC Receipt date])++TimeValue((Left$([1stDOC
Receipt time],2))+":"+(Right$([1stDOC Receipt time],2)))