#ERROR

[Guest]

I am trying to calculate elapsed time.

The first field in the query:

Arrival Date/Time 99/99/0000\ 00:00;;_

The second field in the query:

Now:Now()

Third field:

Dwell Time: DateDiff("n", [Arrival Date/Time], [Now]/60)

I need to show the difference in hours and minutes. Actually, I would like
to have days as well, but not sure which direction to go. Anyway, the above
message is the result of the query.

Help please.

Thanks,
Linda

 

[tina]

as your expression is written, it's attempting to divide the current
date/time (Now()) by 60. try

DateDiff("n", [Arrival Date/Time], [Now])/60

hth

 

[Guest]

Tried that...didn't work. I even took out the whole /60 thing and still get
#ERROR.
I'm wondering if it is because the input mask I am using for arrival
date/time is different than the format for Now(). How do I change the format
for now to show just 99/99/0000 00:00;;_?

as your expression is written, it's attempting to divide the current
date/time (Now()) by 60. try

DateDiff("n", [Arrival Date/Time], [Now])/60

hth

I am trying to calculate elapsed time.

The first field in the query:

Arrival Date/Time 99/99/0000\ 00:00;;_

The second field in the query:

Now:Now()

Third field:

Dwell Time: DateDiff("n", [Arrival Date/Time], [Now]/60)

I need to show the difference in hours and minutes. Actually, I would like
to have days as well, but not sure which direction to go. Anyway, the above
message is the result of the query.

Help please.

Thanks,
Linda

 

[tina]

the format of the date/time value in the field shouldn't matter - unless
you're using the Format() function on the [Arrival Date/Time] field in the
query, which changes the value to a Text data type. if you're not using the
Format() function, then is the [Arrival Date/Time] field a Date/Time data
type in the *table*? if not, you can't do use the DateDiff() date
comparision function on it, because it's not a date value.

hth

Tried that...didn't work. I even took out the whole /60 thing and still get
#ERROR.
I'm wondering if it is because the input mask I am using for arrival
date/time is different than the format for Now(). How do I change the format
for now to show just 99/99/0000 00:00;;_?

as your expression is written, it's attempting to divide the current
date/time (Now()) by 60. try

DateDiff("n", [Arrival Date/Time], [Now])/60

hth

I am trying to calculate elapsed time.

The first field in the query:

Arrival Date/Time 99/99/0000\ 00:00;;_

The second field in the query:

Now:Now()

Third field:

Dwell Time: DateDiff("n", [Arrival Date/Time], [Now]/60)

I need to show the difference in hours and minutes. Actually, I would like
to have days as well, but not sure which direction to go. Anyway, the above
message is the result of the query.

Help please.

Thanks,
Linda

 

[John Spencer]

Pardon me.

What kind of field is Arrival Date/Time? Is it a text field or is it a DateTime field?

Assuming that it is a date field.

DateDiff("n",[Arrival Date/Time], Now()) /60

should work. Note that Now is not included in square brackets [].

Troubleshooting ideas.
-Drop the calculation
-Use a hardcoded date
Try DateDiff("n", #1/1/06#, Now()) and see if that works. If not then I
suspect that you have a problem with your references, or have somehow built a
custom function that is titled Now

-Try just the field and see if any values are null. I can't recall how dateDiff
responds if one of the arguments is null. It might error or it might return
null. I can't test that right now.
You could test that in the query by doing DateDiff("n",Null,#1/1/06#)

Tried that...didn't work. I even took out the whole /60 thing and still get
#ERROR.
I'm wondering if it is because the input mask I am using for arrival
date/time is different than the format for Now(). How do I change the format
for now to show just 99/99/0000 00:00;;_?

as your expression is written, it's attempting to divide the current
date/time (Now()) by 60. try

DateDiff("n", [Arrival Date/Time], [Now])/60

hth

I am trying to calculate elapsed time.

The first field in the query:

Arrival Date/Time 99/99/0000\ 00:00;;_

The second field in the query:

Now:Now()

Third field:

Dwell Time: DateDiff("n", [Arrival Date/Time], [Now]/60)

I need to show the difference in hours and minutes. Actually, I would like
to have days as well, but not sure which direction to go. Anyway, the above
message is the result of the query.

Help please.

Thanks,
Linda

 

[Guest]

OK, I think I've figured out my problem. The Arrival Date/Time field is set
up as a text field. I did this so that I could use an input mask. If I
change the field type to a datetime type, can I still use an input mask in
the form for data entry? If not, what kind of formula do I need to calculate
the elapsed time? I am assuming that if I do not change the field type, I
would also need to convert the current date/time (Now()) to a text fomat
somehow. Jeez, I'm lost here.

Pardon me.

What kind of field is Arrival Date/Time? Is it a text field or is it a DateTime field?

Assuming that it is a date field.

DateDiff("n",[Arrival Date/Time], Now()) /60

should work. Note that Now is not included in square brackets [].

Troubleshooting ideas.
-Drop the calculation
-Use a hardcoded date
Try DateDiff("n", #1/1/06#, Now()) and see if that works. If not then I
suspect that you have a problem with your references, or have somehow built a
custom function that is titled Now

-Try just the field and see if any values are null. I can't recall how dateDiff
responds if one of the arguments is null. It might error or it might return
null. I can't test that right now.
You could test that in the query by doing DateDiff("n",Null,#1/1/06#)

Tried that...didn't work. I even took out the whole /60 thing and still get
#ERROR.
I'm wondering if it is because the input mask I am using for arrival
date/time is different than the format for Now(). How do I change the format
for now to show just 99/99/0000 00:00;;_?

as your expression is written, it's attempting to divide the current
date/time (Now()) by 60. try

DateDiff("n", [Arrival Date/Time], [Now])/60

hth


I am trying to calculate elapsed time.

The first field in the query:

Arrival Date/Time 99/99/0000\ 00:00;;_

The second field in the query:

Now:Now()

Third field:

Dwell Time: DateDiff("n", [Arrival Date/Time], [Now]/60)

I need to show the difference in hours and minutes. Actually, I would
like
to have days as well, but not sure which direction to go. Anyway, the
above
message is the result of the query.

Help please.

Thanks,
Linda

 

[John Spencer]

You should be able to use your input mask with a date time field. My suggestion
is that you backup your database and then try changing the field to a datetime
field. Experiment and see what happens, if all is well - great. If it gets
messy, then use your backup to start over.

OK, I think I've figured out my problem. The Arrival Date/Time field is set
up as a text field. I did this so that I could use an input mask. If I
change the field type to a datetime type, can I still use an input mask in
the form for data entry? If not, what kind of formula do I need to calculate
the elapsed time? I am assuming that if I do not change the field type, I
would also need to convert the current date/time (Now()) to a text fomat
somehow. Jeez, I'm lost here.

Pardon me.

What kind of field is Arrival Date/Time? Is it a text field or is it a DateTime field?

Assuming that it is a date field.

DateDiff("n",[Arrival Date/Time], Now()) /60

should work. Note that Now is not included in square brackets [].

Troubleshooting ideas.
-Drop the calculation
-Use a hardcoded date
Try DateDiff("n", #1/1/06#, Now()) and see if that works. If not then I
suspect that you have a problem with your references, or have somehow built a
custom function that is titled Now

-Try just the field and see if any values are null. I can't recall how dateDiff
responds if one of the arguments is null. It might error or it might return
null. I can't test that right now.
You could test that in the query by doing DateDiff("n",Null,#1/1/06#)

Tried that...didn't work. I even took out the whole /60 thing and still get
#ERROR.
I'm wondering if it is because the input mask I am using for arrival
date/time is different than the format for Now(). How do I change the format
for now to show just 99/99/0000 00:00;;_?

:

as your expression is written, it's attempting to divide the current
date/time (Now()) by 60. try

DateDiff("n", [Arrival Date/Time], [Now])/60

hth


I am trying to calculate elapsed time.

The first field in the query:

Arrival Date/Time 99/99/0000\ 00:00;;_

The second field in the query:

Now:Now()

Third field:

Dwell Time: DateDiff("n", [Arrival Date/Time], [Now]/60)

I need to show the difference in hours and minutes. Actually, I would
like
to have days as well, but not sure which direction to go. Anyway, the
above
message is the result of the query.

Help please.

Thanks,
Linda

 

[Guest]

I have never had this much difficulty with dates and times before. Ok, I
changed the field type to date/time, however when I try to enter the date and
time using the input mask shown below, I get a message saying:

"The value you have entered is not valid for this field."

I tried leaving the format blank, and tried using the general date format,
neither works.

You should be able to use your input mask with a date time field. My suggestion
is that you backup your database and then try changing the field to a datetime
field. Experiment and see what happens, if all is well - great. If it gets
messy, then use your backup to start over.

OK, I think I've figured out my problem. The Arrival Date/Time field is set
up as a text field. I did this so that I could use an input mask. If I
change the field type to a datetime type, can I still use an input mask in
the form for data entry? If not, what kind of formula do I need to calculate
the elapsed time? I am assuming that if I do not change the field type, I
would also need to convert the current date/time (Now()) to a text fomat
somehow. Jeez, I'm lost here.

Pardon me.

What kind of field is Arrival Date/Time? Is it a text field or is it a DateTime field?

Assuming that it is a date field.

DateDiff("n",[Arrival Date/Time], Now()) /60

should work. Note that Now is not included in square brackets [].

Troubleshooting ideas.
-Drop the calculation
-Use a hardcoded date
Try DateDiff("n", #1/1/06#, Now()) and see if that works. If not then I
suspect that you have a problem with your references, or have somehow built a
custom function that is titled Now

-Try just the field and see if any values are null. I can't recall how dateDiff
responds if one of the arguments is null. It might error or it might return
null. I can't test that right now.
You could test that in the query by doing DateDiff("n",Null,#1/1/06#)





Boz wrote:

Tried that...didn't work. I even took out the whole /60 thing and still get
#ERROR.
I'm wondering if it is because the input mask I am using for arrival
date/time is different than the format for Now(). How do I change the format
for now to show just 99/99/0000 00:00;;_?

:

as your expression is written, it's attempting to divide the current
date/time (Now()) by 60. try

DateDiff("n", [Arrival Date/Time], [Now])/60

hth


I am trying to calculate elapsed time.

The first field in the query:

Arrival Date/Time 99/99/0000\ 00:00;;_

The second field in the query:

Now:Now()

Third field:

Dwell Time: DateDiff("n", [Arrival Date/Time], [Now]/60)

I need to show the difference in hours and minutes. Actually, I would
like
to have days as well, but not sure which direction to go. Anyway, the
above
message is the result of the query.

Help please.

Thanks,
Linda

 

[tina]

the Format property and the InputMask property of a field in a table (or a
control in a form) are two completely different things. make sure you're
working with the Input Mask property, and in (table or form) design view,
press F1 while in the property to get the InputMask topic in Access Help,
which is very informative.

hth

I have never had this much difficulty with dates and times before. Ok, I
changed the field type to date/time, however when I try to enter the date and
time using the input mask shown below, I get a message saying:

"The value you have entered is not valid for this field."

I tried leaving the format blank, and tried using the general date format,
neither works.

You should be able to use your input mask with a date time field. My suggestion
is that you backup your database and then try changing the field to a datetime
field. Experiment and see what happens, if all is well - great. If it gets
messy, then use your backup to start over.

OK, I think I've figured out my problem. The Arrival Date/Time field is set
up as a text field. I did this so that I could use an input mask. If I
change the field type to a datetime type, can I still use an input mask in
the form for data entry? If not, what kind of formula do I need to calculate
the elapsed time? I am assuming that if I do not change the field type, I
would also need to convert the current date/time (Now()) to a text fomat
somehow. Jeez, I'm lost here.

:

Pardon me.

What kind of field is Arrival Date/Time? Is it a text field or is it a DateTime field?

Assuming that it is a date field.

DateDiff("n",[Arrival Date/Time], Now()) /60

should work. Note that Now is not included in square brackets [].

Troubleshooting ideas.
-Drop the calculation
-Use a hardcoded date
Try DateDiff("n", #1/1/06#, Now()) and see if that works. If not then I
suspect that you have a problem with your references, or have somehow built a
custom function that is titled Now

-Try just the field and see if any values are null. I can't recall how dateDiff
responds if one of the arguments is null. It might error or it might return
null. I can't test that right now.
You could test that in the query by doing DateDiff("n",Null,#1/1/06#)





Boz wrote:

Tried that...didn't work. I even took out the whole /60 thing and still get
#ERROR.
I'm wondering if it is because the input mask I am using for arrival
date/time is different than the format for Now(). How do I change the format
for now to show just 99/99/0000 00:00;;_?

:

as your expression is written, it's attempting to divide the current
date/time (Now()) by 60. try

DateDiff("n", [Arrival Date/Time], [Now])/60

hth


I am trying to calculate elapsed time.

The first field in the query:

Arrival Date/Time 99/99/0000\ 00:00;;_

The second field in the query:

Now:Now()

Third field:

Dwell Time: DateDiff("n", [Arrival Date/Time], [Now]/60)

I need to show the difference in hours and minutes. Actually, I would
like
to have days as well, but not sure which direction to go. Anyway, the
above
message is the result of the query.

Help please.

Thanks,
Linda

 

[Guest]

Thanks for everyone' help, but I figured out the problem. The input mask I
was using was wrong.

Was: 99/99/0000\ 00:00;;_
Changed to: 99/99/0000\ 00:00;0_

Not real sure why the change worked, but it did.

Thanks again. You guys (and girls) are a tremendous help.

the Format property and the InputMask property of a field in a table (or a
control in a form) are two completely different things. make sure you're
working with the Input Mask property, and in (table or form) design view,
press F1 while in the property to get the InputMask topic in Access Help,
which is very informative.

hth

I have never had this much difficulty with dates and times before. Ok, I
changed the field type to date/time, however when I try to enter the date and
time using the input mask shown below, I get a message saying:

"The value you have entered is not valid for this field."

I tried leaving the format blank, and tried using the general date format,
neither works.

You should be able to use your input mask with a date time field. My suggestion
is that you backup your database and then try changing the field to a datetime
field. Experiment and see what happens, if all is well - great. If it gets
messy, then use your backup to start over.



Boz wrote:

OK, I think I've figured out my problem. The Arrival Date/Time field is set
up as a text field. I did this so that I could use an input mask. If I
change the field type to a datetime type, can I still use an input mask in
the form for data entry? If not, what kind of formula do I need to calculate
the elapsed time? I am assuming that if I do not change the field type, I
would also need to convert the current date/time (Now()) to a text fomat
somehow. Jeez, I'm lost here.

:

Pardon me.

What kind of field is Arrival Date/Time? Is it a text field or is it a DateTime field?

Assuming that it is a date field.

DateDiff("n",[Arrival Date/Time], Now()) /60

should work. Note that Now is not included in square brackets [].

Troubleshooting ideas.
-Drop the calculation
-Use a hardcoded date
Try DateDiff("n", #1/1/06#, Now()) and see if that works. If not then I
suspect that you have a problem with your references, or have somehow built a
custom function that is titled Now

-Try just the field and see if any values are null. I can't recall how dateDiff
responds if one of the arguments is null. It might error or it might return
null. I can't test that right now.
You could test that in the query by doing DateDiff("n",Null,#1/1/06#)





Boz wrote:

Tried that...didn't work. I even took out the whole /60 thing and still get
#ERROR.
I'm wondering if it is because the input mask I am using for arrival
date/time is different than the format for Now(). How do I change the format
for now to show just 99/99/0000 00:00;;_?

:

as your expression is written, it's attempting to divide the current
date/time (Now()) by 60. try

DateDiff("n", [Arrival Date/Time], [Now])/60

hth


I am trying to calculate elapsed time.

The first field in the query:

Arrival Date/Time 99/99/0000\ 00:00;;_

The second field in the query:

Now:Now()

Third field:

Dwell Time: DateDiff("n", [Arrival Date/Time], [Now]/60)

I need to show the difference in hours and minutes. Actually, I would
like
to have days as well, but not sure which direction to go. Anyway, the
above
message is the result of the query.

Help please.

Thanks,
Linda

 

[John Spencer]

Adding the zero told Access to store the separation characters that you were
not typing into the control. Before, Access was told to store only the
characters you were typing.

So to enter Dec 1, 2006 at 12:30 PM you were typing "120120061230". The
control was then attempting to assign that to the date field. When you
added the 0 to the input mask then slashes, space and colon were added to
the string that was being assigned to the field "12/01/2005 12:30".

From Online help on InputMask Property - Argument Two
Specifies whether Microsoft Access stores the literal display characters in
the table when you enter data. If you use 0 for this section, all literal
display characters (for example, the parentheses in a phone number input
mask) are stored with the value; if you enter 1 or leave this section blank,
only characters typed into the control are stored.

Thanks for everyone' help, but I figured out the problem. The input mask
I
was using was wrong.

Was: 99/99/0000\ 00:00;;_
Changed to: 99/99/0000\ 00:00;0_

Not real sure why the change worked, but it did.

Thanks again. You guys (and girls) are a tremendous help.

the Format property and the InputMask property of a field in a table (or
a
control in a form) are two completely different things. make sure you're
working with the Input Mask property, and in (table or form) design view,
press F1 while in the property to get the InputMask topic in Access Help,
which is very informative.

hth

I have never had this much difficulty with dates and times before. Ok,
I
changed the field type to date/time, however when I try to enter the
date and
time using the input mask shown below, I get a message saying:

"The value you have entered is not valid for this field."

I tried leaving the format blank, and tried using the general date
format,
neither works.

:

You should be able to use your input mask with a date time field. My suggestion
is that you backup your database and then try changing the field to a datetime
field. Experiment and see what happens, if all is well - great. If
it gets
messy, then use your backup to start over.



Boz wrote:

OK, I think I've figured out my problem. The Arrival Date/Time
field is set
up as a text field. I did this so that I could use an input mask.
If I
change the field type to a datetime type, can I still use an input mask in
the form for data entry? If not, what kind of formula do I need to calculate
the elapsed time? I am assuming that if I do not change the field type, I
would also need to convert the current date/time (Now()) to a text fomat
somehow. Jeez, I'm lost here.

:

Pardon me.

What kind of field is Arrival Date/Time? Is it a text field or
is it a DateTime field?

Assuming that it is a date field.

DateDiff("n",[Arrival Date/Time], Now()) /60

should work. Note that Now is not included in square brackets
[].

Troubleshooting ideas.
-Drop the calculation
-Use a hardcoded date
Try DateDiff("n", #1/1/06#, Now()) and see if that works. If
not then I
suspect that you have a problem with your references, or have somehow built a
custom function that is titled Now

-Try just the field and see if any values are null. I can't
recall how dateDiff
responds if one of the arguments is null. It might error or it might return
null. I can't test that right now.
You could test that in the query by doing DateDiff("n",Null,#1/1/06#)





Boz wrote:

Tried that...didn't work. I even took out the whole /60 thing
and still get
#ERROR.
I'm wondering if it is because the input mask I am using for arrival
date/time is different than the format for Now(). How do I
change the format
for now to show just 99/99/0000 00:00;;_?

:

as your expression is written, it's attempting to divide the current
date/time (Now()) by 60. try

DateDiff("n", [Arrival Date/Time], [Now])/60

hth


I am trying to calculate elapsed time.

The first field in the query:

Arrival Date/Time 99/99/0000\ 00:00;;_

The second field in the query:

Now:Now()

Third field:

Dwell Time: DateDiff("n", [Arrival Date/Time], [Now]/60)

I need to show the difference in hours and minutes.
Actually, I would
like
to have days as well, but not sure which direction to go. Anyway, the
above
message is the result of the query.

Help please.

Thanks,
Linda