Date (1 Viewer)

[Wallie]

Hey,

I have a question about using a date in a query.
I have a searchform inwhich a date can be enterred. The searchresults are the records > given date.
The problem is I don't know how to this and another thing is, I'm from Holland so I have the dutch MS Access. The date saved in a record looks like this: dd-mm-yyyy and not the US version :mm-dd-yyyy.
SQL only understands US dates so this wont work:
SELECT blahblah
From table
Where((

.[date])=IIf(IsNull([Forms]![form]![date]),[date],[Forms]![form]![date]))
Any ideas how to solve this problem ?

Thanx....

 

[RV]

This is a regular question.
Search the forum for former posts.
I'm sure you'll find an answer to your question

>SQL only understands US dates<

Not entirely true, look here:
http://www.access-programmers.co.uk/ubb/Forum3/HTML/001076.html

RV

 

[Rich]

Once again someone has a field named Date! and you are telling access if that field is Null then to use it anyway in your example??
Try
Where((

.[Flddate])=IIf(IsNull([Forms]![form]![Flddate]),Date(),[Forms]![form]![Flddate]))

 

[Wallie]

Hey,

I know this question may have been asked billion of times, but I couldn't find the answer I'm looking for.
So maybe someone can help me out, because I've been trying a lot of things, without result.
On my searchform I'd like to seach on a date > the date given in the form.
The date stored in the table is:dd-mm-yyyy.
I've tried these options:
SELECT *
FROM tbl
WHERE tbl.datum > [Form]![frm]![fieldDate]
*wrong*
SELECT *
FROM tbl
WHERE tbl.datum > "#" & Format([Forms]![frm]![fieldDate],"mm") & "-" & Format([Forms]![frm]![fieldDate],"dd") & "-" & Format([Forms]![frm]![fieldDate],"yyyy") & "#"
*wrong*
Some variations on the one above
*wrong*
But when i use this:
SELECT *
FROM tbl
WHERE tbl.datum Like "15-02-2002"
*Works, but not what I want*
Can someone help me with this or give a reference to a simular problem.

Thanx alot....

 

[Rich]

You cannot set criteria on the asterisk, you have to add your date field
SELECT Account.*, Account.AccDate
FROM Account
WHERE (((Account.AccDate)>[EnterStartDate]));

 

[Wallie]

:-(

SELECT tbl.*, tbl.datum
FROM tbl
WHERE (((tbl.datum)>[Froms]![frm]![fieldDate]));

Still not right, still no searchresults....but thanx anyways

 

[Rich]

Are your dates defined as ShortDate or are you formatting them to dd-mm-yyyy, what are your regional settings in Windows, are you using a query or strSQL?

[This message has been edited by Rich (edited 04-10-2002).]

 

[RV]

Rich,

>You cannot set criteria on the asterisk, you have to add your date field<

Since when is it that one cannot set criteria on the asterisk?
I guess you are wrong here....

RV

 

[Wallie]

Are your dates defined as ShortDate or are you formatting them to dd-mm-yyyy, what are your regional settings in Windows, are you using a query or strSQL?

The problem was that in my table the field 'datum' was set to 'text' and not to ShortDate ;-) So that problem is solved.....Thanx.
But when no date is entered in the textfield it should return all record nomatter what date. I figured something out like this:
tbl.datum>IIf(IsNull([Forms]![frm]![fieldDate],"#1-1-2000#",[Forms]![frm]![fieldDate]))
Instead of #1-1-2000#" iv'e tried several things:
"01-01-2000", "1-1-2000", "#01-01-2000#"....but no luck.

Ideas...

Thanx for all the help btw....

 

[Rich]

RV
MS Error message
You Cannot specify criteria for the asterisk (*)
Beacause the asterisk represents all the fields in the table etc.etc.
perhaps you could show me how to overcome this.


Wallie remove the quotes enclosing the pounds
>IIf(IsNull([Forms]![frm]![fieldDate],#01-01-2000#,[Forms]![frm]![fieldDate]))

 

[Wallie]

Wallie remove the quotes enclosing the pounds
>IIf(IsNull([Forms]![frm]![fieldDate],#01-01-2000#,[Forms]![frm]![fieldDate]))

Still no luck....I think I've tried all possible options here.....

[This message has been edited by Wallie (edited 04-10-2002).]

 

[Rich]

Does the query work if you enter 01/01/2000 in the form or change 01-01-2000 to 01/01/2000?

 

[Wallie]

Nope....still nuttin'

 

[Rich]

OK what happens if you just put >[Forms]![frm]![fieldDate] in the criteria and then enter the date on the form?

 

[RV]

Rich,

>Beacause the asterisk represents all the fields in the table etc.etc.<

So logically you CAN set criteria as you've already refered to all columns in a table....

In the statement you posted earlier to Wallie,you're referring twice to one and the same column in a table:

SELECT Account.*, Account.AccDate
FROM Account
WHERE (((Account.AccDate)>[EnterStartDate]));

Basic (ANSI) SQL, I've been told.

Greetings,

RV