Simple query won't work with criteria!

[Les Isaacs]

Hi All

This works perferctly ...

SELECT DateDiff("d",[Sickness - Start Date],[Sickness - End Date]) AS Expr2
FROM tblCount, [qry Submissionsheets] INNER JOIN staffs ON [qry Submissionsheets].[Employee Name] = staffs.staff_name
WHERE (((IsDate([Sickness - Start Date]))=True) AND ((IsDate([Sickness - End Date]))=True));

... but when I add a criteria of >3 to the field Expr2 I get "Data type mismatch in criteria expression"

How can this be - I have checked that all the [Sickness - Start Date] and [Sickness - End Date] values are valid dates, so surely the DateDiff must return an integer - which it appears to do when I run the query without the criteria on the DateDiff field: so why can't I add a criteria to that DateDiff field:banghead:

Hope someone can help.
Many thanks
Les

 

[spikepl]

Show the SQL of that query with all criteria. If it won't let you change to SDQL-view, take a screen shot of it in the query designer and show that.

I notice that you do not have any consistent naming convention - a mix of blanks, "_" .... I would recommend that you choose one and stick with it, otherwise you'll have eternal problems.

You may have heard that spaces are not recommended.

I personally find "-" a pain to type and prefer concatenated words like StaffName without any non-alphanumeric characters.


Using mixed (or camelCase) has the additional advantage that when you type everything in lowercase in VBA , the interpreter will convert the case to fit the definition. If it doesn't then you know straigth away that you have mistyped something.


Also, a DateTime field in a table can either hold a date or Null.

So

Not MyDate Is NULL is a sufficient criterion for getting a field with a date in it. Provided the field is of type DateTime.

 

[Les Isaacs]

Hi Spikepl

Thanks for your response. The sql of the query that crashes is:

SELECT DateDiff("d",[Sickness - Start Date],[Sickness - End Date]) AS Expr2
FROM tblCount, [qry Submissionsheets] INNER JOIN staffs ON [qry Submissionsheets].[Employee Name] = staffs.staff_name
WHERE (((IsDate([Sickness - Start Date]))=True) AND ((IsDate([Sickness - End Date]))=True) AND ((DateDiff("d",[Sickness - Start Date],[Sickness - End Date]))<3));

I appreciate your comments about the naming convention (or lack!) in the above ... but I'm only partly to blame. I inherited this accdb some time ago, and am gradually implementing a naming convention.

Hope you can see what I need to do to get the query to work with the criteria on the DateDiff field.

Thanks again
Les

 

[spikepl]

What is tblCount doing in there?

Can you show all the data, meaning also what qrySubmissionsheets contains? I do not see anything wrong with the <3 criterion.

IsDate([Sickness - Start Date]) returns True or False, so

IsDate([Sickness - Start Date])

will in the query yield the same as

IsDate([Sickness - Start Date]) =True

As mentioned before

NOT [Sickness - Start Date] IS NULL is quite adequate

Another comment: you are joining on staffname. That is an unusual solution to use staffname as primary key, for a million of reasons.

 

[Les Isaacs]

Hi spikepl

Thanks for your further reply.

tblCount is a single field table, with 1000 records with values 1,2,3,4 ... 1000: I use it in this query (and others) to generate a record for each day in the range [Sickness - Start Date] to [Sickness - End Date] (for if [Sickness - Start Date] = 7 April 2012 and [Sickness - End Date] = 15 April 2012 the query will add 9 records (to a separate table- where other things are then done). 1000 is rather more than the maximum date range that will ever have to be dealt with.

qrySubmissionsheets is in fact a union query of two tables, and contains various fields, two of which are [Sickness - Start Date] and [Sickness - End Date], and for the data in question I have checked visually that both these fields contain valid dates - and of course I'm also checking with the IsDate function. I appreciate that I could instead have used NOT [Sickness - Start Date] IS NULL.

You have rumbled me on having made staff_name a key field. Believe it or not, it used to be 'name'. I will eventually grasp the nettle and use a 'serial' key, but not this week!

I have created and attached an accdb with a cutdown version of the tables and queries, as requested (please note the key fields and relationships have been altered for simplicity and anonimity). Hopefully this will enable you to see exactly what's going on - and more importantly how to fix it.

Thanks once again
Les

 

[spikepl]

Your dates are datetime in one table and text in another. It's a miracle that the union query runs without criteria.

This kind of stuff is the basics that you should have in order before proceeding. I recommend that you cease adding code and bring you data and naming in order. This will save you lots of aggro now and later. Have a look here http://www.access-programmers.co.uk/forums/showthread.php?p=1152269#post1152269

 

[spikepl]

I seem to recall mentioning to you, that the way to construct complex things is to make sure that each part works by itself, prior to adding it to the complex thing.

As you can see, this is still valid. A lot of small steps will bring you to your goals much faster than one giant leap (followd by retracing your steps). Any problem? Then go back to basics and add small verified blocks, one at a time.

Next time, bring more creative errors rather than this run-of-the-mill variety.. You should be able to, by now.

 

[Les Isaacs]

Hi Spikepl

Thanks for your further replies.

First, let me make clear that I really do appreciate the time that you (and others) put into helping us less experienced 'developers' in these forums. I have gained a lot of help from responses I have recieved, and have also added significantly to my knowledge in various areas.

That said, I didn't find your last two posts helpful, as they are (necessarily) made without knowledge of the context of the problem and for this reason your suggestions can't be applied. Obviously I could have given more context, but this would have made the post very long and may have been regarded as irrelevant (at least by the majority of would-be reponders).

The relevant part of the context here is that I am dealing with a 14 year old, very complex application that is in daily use at the heart of a small business that employs 11 people. The system generally works perfectly well, and the problems only arise as I attempt to refine the system or add new faclilities. Very obviously the ideal would be to go back to basics and re-write the system (as there are some naming and other issues that can make life difficult), but this is the real world and the business cannot afford to fund a re-write, and has made the decision that continuing to work with the existing system is the only practical way forward. I would repeat that the problems that arise are rarely serious and have always been overcome notwithstanding the imperfect basics. Your suggestion to "cease coding" is unfortunately not at all realistic, nor appropriate, within this context.

I hope very much that this reply does not cause any offence, as I genuinely do feel a lot of gratitude to you experts who help us novices. In this case, however, I would ask that you might perhaps have a little more respect for the context from which the problem was presented, and that the businesse's decision to continue to work with the existing system - including fixing problems like the one presented in this thread - is the correct decision, as it is made by those closest to all the relevant information.

Thanks as ever
Les