Datediff in a Report using linked tables? 3

[frontside]

The linked table has a [LevDate] field formatted like text, I need to use this field in a Datediff function(In a Report)! How do I change the format so the datediff function will work?

I tried to give the tablefield a format like "yyyymmdd" but that didnt work.
Since this is a linked table I cant just change to date format in the properties for the tablefield.

Any ideas? I hope this is a regular problem and that someone has a good solution to my problem.

 

[KenReay]

Hi

You need the function CDate()

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[frontside]

Thank´s Ken
Since my VB knowledge aint that good I have two more questions.
Q 1.
Where Exactly should I put the CDate funcion? In open report? like Me.date = CDate([date])

Q 2.
Is it possible to use this CDate function to change the format like the Example below?
2002-04-04 = 0204

Mike "sweden"

 

[KenReay]

Q 1.
Where Exactly should I put the CDate funcion? In open report? like Me.date = CDate([date])

Q 2.
Is it possible to use this CDate function to change the format like the Example below?
2002-04-04 = 0204

Q1 - Wherever you were going to use the datediff() function use datediff("d",CDate([LevDate]),Date()) or whatever

OR

Base you report on a query, and in the query have a calculated column xDate:CDate([LevDate])

the above was assuming that levdate was in dd/mm/yyyy form (or whatever your local seeting says is a date format), but see below

Q2 if it is in yyyymmdd form then

CDate(Right([LevDate],2) & "/" & Mid([LevDate],5,2) & "/" & Left([LevDate],4)

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[frontside]

I still have some problem with the Datediff solution. I´ve posted a test database

http://home.no/frontside900/

so anyone can download and see if they could solve the problem

(I will post the solution to the problem at this address when/if I get it)

Regards

Mikael "Sweden"

 

[Jahappz]

I have a problem that would be solved if
"if it is in yyyymmdd form then

CDate(Right([LevDate],2) & "/" & Mid([LevDate],5,2) & "/" & Left([LevDate],4)

"

that code had worked
it works fine if i use CDate[Levdate]

but when i add "right and ,2" all i get is an error
í tried it in both an Query and in a form.

Anyone has any ideas why it doesnt work?
i added a sample at "frontside900"'s access page if you want to look

/thnx
Andreas

 

[KenReay]

Hi

Worked examples Emailed to you both Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[frontside]

Thank´s KenReay for all the help!! The code looked great, Exactly what I was looking for!

I posted the solution at

http://home.no/frontside900/

Thanks again

Mike

 

[Duvy]

Hello

Checked in your base and date1 & date2 should first converted in a date format before getting the diff.
I place this on "Detalj_Format" event :

Me.Dat1 = CDate(Left([Datum1], 4) & "/" & Mid([Datum1], 5, 2) & "/" & Right([Datum1], 2))
Me.dat2 = CDate(Left([Datum2], 4) & "/" & Mid([Datum2], 5, 2) & "/" & Right([Datum2], 2))
Me.Texte28 = DateDiff("d", [Dat1], [dat2])

Hope this help,
Yvon Duvivier

 

[Jahappz]

Big thanx to you Ken Reay