Quick SQL Help needed please

[cluelessgenius]

hey guys,

 

so im working with MS ACCESS 2013 and here is my sql query:

 

SELECT        Kennzeichen, Kategorie, Fahrerlaubnis, TÜV, Service, Defekt, DefektArt, DefektDatum, Hebebuhne, HebebuhneTuv, IIF(ISNULL(HebebuhneTuv) OR HebebuhneTuv = "", '30.12.9999', HebebuhneTuv) AS ttuv  

FROM            Fahrzeuge 

WHERE (CDate(ttuv) <= CDate(?))

 

firstly sorry for the german field names. secondly HebebuhneTuv represents a date ind varchar form and i want to raplace all empty or null values with the super high value and then ask to show only those entry where the date is smaller than today.

 

problem is access doesnt recognize the custom field name ttuv in the where clause and is asking me for its value. all i want to do is exclude entry with null or empty values. it cant be this hard can it?

 

thanks for any help at all

[kichilron]

Talk me through how you're trying to define the ttuv, exactly.

 

I'm not sure your "AS" statement is valid here. But it's way too early for my brain.

 

Nice quote in your signature, by the way.

[xTrekStorex]

 

hey guys,

 

so im working with MS ACCESS 2013 and here is my sql query:

 

SELECT        Kennzeichen, Kategorie, Fahrerlaubnis, TÜV, Service, Defekt, DefektArt, DefektDatum, Hebebuhne, HebebuhneTuv, IIF(ISNULL(HebebuhneTuv) OR HebebuhneTuv = "", '30.12.9999', HebebuhneTuv) AS ttuv  

FROM            Fahrzeuge 

WHERE (CDate(ttuv) <= CDate(?))

 

firstly sorry for the german field names. secondly HebebuhneTuv represents a date ind varchar form and i want to raplace all empty or null values with the super high value and then ask to show only those entry where the date is smaller than today.

 

problem is access doesnt recognize the custom field name ttuv in the where clause and is asking me for its value. all i want to do is exclude entry with null or empty values. it cant be this hard can it?

 

thanks for any help at all

 

I also would like some definition of ttuv.

I mean.. isnt AS just for output? can you actually read the table-alias in the query it's created itself (-> "WHERE (CDate(ttuv)")?

I'd split this into 2 queries: one for the exclude and one to read from the resulting table

[cluelessgenius]

Talk me through how you're trying to define the ttuv, exactly.

 

I'm not sure your "AS" statement is valid here. But it's way too early for my brain.

 

Nice quote in your signature, by the way.

thanks yeah its a little early for me too maybe thats why i dont see it.

so basicly im just trying to get all entries where HebebuhneTuv is smaller than a date of my choosing. the problem is that HebebuhneTuv also can be null or empty in which case CDate would throw an exception so i have to exclude all entries with null or empty

[cluelessgenius]

I also would like some definition of ttuv.

I mean.. isnt AS just for output? can you actually read the table-alias in the query it's created itself (-> "WHERE (CDate(ttuv)")?

I'd split this into 2 queries: one for the exclude and one to read from the resulting table

yeah that seems to be the problem using ttuv in where clause doesnt work i redid the whole a little and now it seems to work 

 

SELECT        Kennzeichen, Kategorie, Fahrerlaubnis, TÜV, Service, Defekt, DefektArt, DefektDatum, Hebebuhne, HebebuhneTuv

FROM            Fahrzeuge 

WHERE CDate(IIF(ISNULL(HebebuhneTuv) OR HebebuhneTuv = "", '30.12.9999', HebebuhneTuv)) <= CDate(@Date)

[Ciccioo]

the problem here is in the table structure itself: if HebebuhneTuv is a date, then why are you storing it as a string?

having it as a string forces you to write weird hacks like this, makes it possible for the db to enter a corrupted state, and makes this query much much slower because access can't use any index

[cluelessgenius]

the problem here is in the table structure itself: if HebebuhneTuv is a date, then why are you storing it as a string?

having it as a string forces you to write weird hacks like this, makes it possible for the db to enter a corrupted state, and makes this query much much slower because access can't use any index

the db behind it is not for me to change and i also didnt design it. i also am very confused and sometimes angry because he stored everything like this. also he didnt use foreign keys or any relations whatsoever. there are no constraints. this db is a mess but i gots to work with it and make the best with what i have ..... oh and kill the original designer obviously

[Ciccioo]

the db behind it is not for me to change and i also didnt design it. i also am very confused and sometimes angry because he stored everything like this. also he didnt use foreign keys or any relations whatsoever. there are no constraints. this db is a mess but i gots to work with it and make the best with what i have ..... oh and kill the original designer obviously

you get to be the violent psychopath this time around, rightfully so