Data Type Mismatch in Criteria expression

  • Thread starter Gaetanm via AccessMonster.com
  • Start date
G

Gaetanm via AccessMonster.com

Please help!

This is a Query and I'm trying to get information about a employee
The following code works then there is the BUT!

SELECT Clock_Table.EmployeeID, Clock_Table.StartDate, Clock_Table.StopDate,
DateDiff("n",[Startdate],[Stopdate])\60 & Format(DateDiff("n",[Startdate],
[Stopdate]) Mod 60,"\:00") AS ToTalTime, DateDiff("n",[Startdate],[Stopdate])
AS TotalHoursMinutes, Round([TotalHoursMinutes]/60,2) AS HundredTime
FROM Clock_Table;

When I Add the WHERE statement and move the ';' I get
"DATA TYPE MISMATCH IN CRITERIA EXPRESSION"

Here is my added WHERE statement:
WHERE EmployeeID = '1';

I even used WHERE Clock_Table.EmployeeID = '1';

Can you please help

Gaetanm
 
B

Brendan Reynolds

Perhaps EmployeeID is a numeric field, not a text field, in which case
you'll need to remove the quotes. Change this ...

WHERE EmployeeID = '1'

.... to this ...

WHERE EmployeeID = 1
 
G

Gaetanm via AccessMonster.com

Brendan said:
Perhaps EmployeeID is a numeric field, not a text field, in which case
you'll need to remove the quotes. Change this ...

WHERE EmployeeID = '1'

... to this ...

WHERE EmployeeID = 1
Please help!
[quoted text clipped - 20 lines]

Thank you Brenda for the quick and effective reply. I'm new at this and
taking one step at a time.

Below I have changed my where statement to include another parameter
but it comes up with a dialouge box that ask:

Enter Parameter Value

time_value.StopeDate

WHERE (((time_total.StopDate)>Date()-Weekday(Date()+1))) And EmployeeID = 1;

Brenda what is the problem with it and more importently what is the logic
flow so I could understand it.

"Give a man a fish to eat you feed him for one day, teach him how to fish you
feed him for a life time"

Gaetanm
 
G

Gaetanm via AccessMonster.com

Gaetanm said:
Perhaps EmployeeID is a numeric field, not a text field, in which case
you'll need to remove the quotes. Change this ...
[quoted text clipped - 10 lines]
Thank you Brenda for the quick and effective reply. I'm new at this and
taking one step at a time.

Below I have changed my where statement to include another parameter
but it comes up with a dialouge box that ask:

Enter Parameter Value
time_value.StopeDate

WHERE (((time_total.StopDate)>Date()-Weekday(Date()+1))) And EmployeeID = 1;

Brenda what is the problem with it and more importently what is the logic
flow so I could understand it.

"Give a man a fish to eat you feed him for one day, teach him how to fish you
feed him for a life time"

Gaetanm

Hi Brenda I just figuerd it out Im using "time_total.stopdate" instead of
Clock_Table.stopdate

Thanks noe I can eat my fish

Gaetanm
 
B

Brendan Reynolds

Well, I don't know what 'Brenda', whoever she is, might think, but
personally I think that if this is the only change you have made to your
query since you posted it, it does not include any table named 'time_total'
in its FROM clause.

As for the logic, that's too big a subject for a newsgroup post, but you
could start with this article from Microsoft ...

http://office.microsoft.com/en-us/access/HP051884001033.aspx

--
Brendan Reynolds
Access MVP

Gaetanm via AccessMonster.com said:
Brendan said:
Perhaps EmployeeID is a numeric field, not a text field, in which case
you'll need to remove the quotes. Change this ...

WHERE EmployeeID = '1'

... to this ...

WHERE EmployeeID = 1
Please help!
[quoted text clipped - 20 lines]

Thank you Brenda for the quick and effective reply. I'm new at this and
taking one step at a time.

Below I have changed my where statement to include another parameter
but it comes up with a dialouge box that ask:

Enter Parameter Value

time_value.StopeDate

WHERE (((time_total.StopDate)>Date()-Weekday(Date()+1))) And EmployeeID =
1;

Brenda what is the problem with it and more importently what is the logic
flow so I could understand it.

"Give a man a fish to eat you feed him for one day, teach him how to fish
you
feed him for a life time"

Gaetanm
 
G

Gaetanm via AccessMonster.com

Brendan said:
Well, I don't know what 'Brenda', whoever she is, might think, but
personally I think that if this is the only change you have made to your
query since you posted it, it does not include any table named 'time_total'
in its FROM clause.

As for the logic, that's too big a subject for a newsgroup post, but you
could start with this article from Microsoft ...

http://office.microsoft.com/en-us/access/HP051884001033.aspx
[quoted text clipped - 32 lines]
Brendan

Thank you for your reply. I myself do not who Brenda is but
she appears to be courteous and willing to help.

In this world of change I made a mistake in my retyping my query
and hence the confusion about Time_total but I did find the error
and reposted.

The logic was not to big for this news group post. The logical explanation
is as you exquisitely expressed above “query since you posted it, it does not
include any table named 'time_total' in its FROM clause.â€


I would like also to take this time to thank everyone else that has helped
me in this project.


Gaetanm
 
G

Gaetanm via AccessMonster.com

Gaetanm said:
[quoted text clipped - 9 lines]
Enter Parameter Value
time_value.StopeDate
[quoted text clipped - 7 lines]

Hi Brenda I just figuerd it out Im using "time_total.stopdate" instead of
Clock_Table.stopdate

Thanks noe I can eat my fish

Gaetanm
Hi Brenda

I'm moving along and understanding a bit better I was wondering if
you could help me to extend this query?

I’m having a problem with my WHERE statement in a query.

This is what I have:

The form is frmclock_Stop_table with a combo box [EmployeeID], and a text box
called [Hours worked].

I would like to query the forms combo box for the Employee calculate the
total hours worked this week for that Employee and put the total hours worked
in the text box via a Dlookup of the query in the text box.
If that’s the right way to go.

The following will give me everyone worked this week:

SELECT Clock_Table.EmployeeID, Clock_Table.StartDate, Clock_Table.StopDate,
DateDiff("n",[Startdate],[Stopdate])\60 & Format(DateDiff("n",[Startdate],
[Stopdate]) Mod 60,"\:00") AS ToTalTime, DateDiff("n",[Startdate],[Stopdate])
AS TotalHoursMinutes, Round([TotalHoursMinutes]/60,2) AS HundredTime
FROM Clock_Table
WHERE ((([Clock_Table].[StopDate])>Date()-Weekday(Date()+1)));

When I change my WHERE statement to the one below I get all me records as if

The WHERE statement was non existent.

WHERE ((([Clock_Table].[StopDate])>Date()-Weekday(Date()+1))) & " EmployeeID
= [Forms]![frmClock_Start_Table]![cboEmployeeId.column(0)]";



PS I know my code still is not completed yet for the sum of total minutes to
be Converted to hours for that Employee.

“A journey of a thousand miles starts with the first stepâ€

Gaetanm
 

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

Weekday 0
Cartesian Help Please 5
Query and time 0
MOD 60 8
Query that seems impossible 8
Unique value in query 12
Dlookup fails with 2 compo boxes 2
Sum(DateDiff 4

Top