Addition to Query

[Bob]

I want to add this to my Query: if [tblHorseInfo,Status] shows "Finished"
to show "Finished" In [TableHorseInfo,StatusDate]

SELECT tblHorseInfo.HorseID, tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True) AS Expr1,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Expr2, tblHorseInfo.Invocing,
tblHorseInfo.StatusDate
FROM tblHorseInfo
ORDER BY tblHorseInfo.Status, funGetHorse(0,tblHorseInfo.HorseID,True);

Thanks for any help on this...............Bob

 

[Allen Browne]

Bob, there is a problem here with data types.

If you open tblHorseInfo in design veiw, and choose the StatusDate field, it
is a Date/Time type? If so, the word "Finished" is not a valid value for the
field.

It is possible to create a query that has the word "Finished" in among the
date fields. However, Access will treat this field as Text. Any criteria you
use won't work right. And if you sort by the field, it will be all wrong.

The best solution might be to suppress the date if it is finished. A
Date/Time field can contain a Null, so that doesn't mess up the data type.
To do that, type an expression like this into the Field row of your query,
in query design:
DisplayDate: CVDate(IIf([tblHorseInfo].[Status] = 'Finished', Null,
[tblHorseInfo].StatusDate]))
(That's all one line.)

That should work unless Status is actually a Number field (typically if you
used the Lookup wizard.)

 

[Bob]

Thanks Allen I changed "Finished" to "Active" that is giving every horse in
Finished Mode a Date next to its name, My Combo box on the form is showing
HorseID with their date if they are in finished mode, how would I filter it
so as it only shows the data relevant to HorseID on that form .....Thanks
....Bob, StatusDate was missing [

Bob, there is a problem here with data types.

If you open tblHorseInfo in design veiw, and choose the StatusDate field,
it is a Date/Time type? If so, the word "Finished" is not a valid value
for the field.

It is possible to create a query that has the word "Finished" in among the
date fields. However, Access will treat this field as Text. Any criteria
you use won't work right. And if you sort by the field, it will be all
wrong.

The best solution might be to suppress the date if it is finished. A
Date/Time field can contain a Null, so that doesn't mess up the data type.
To do that, type an expression like this into the Field row of your query,
in query design:
DisplayDate: CVDate(IIf([tblHorseInfo].[Status] = 'Finished', Null,
[tblHorseInfo].StatusDate]))
(That's all one line.)

That should work unless Status is actually a Number field (typically if
you used the Lookup wizard.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to add this to my Query: if [tblHorseInfo,Status] shows
"Finished" to show "Finished" In [TableHorseInfo,StatusDate]

SELECT tblHorseInfo.HorseID, tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True) AS Expr1,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Expr2,
tblHorseInfo.Invocing, tblHorseInfo.StatusDate
FROM tblHorseInfo
ORDER BY tblHorseInfo.Status, funGetHorse(0,tblHorseInfo.HorseID,True);

Thanks for any help on this...............Bob

 

[Bob]

Does this seem ok to you it is working, except it is not putting in todays
date

=NZ(DLookUp("DisplayDate","qryFinishedDate","HorseID =" &
tbHorseID.Value),"")

Can this code be altered to enter todays date?
DisplayDate:
CVDate(IIf(tblHorseInfo.Status='Active',Null,tblHorseInfo.StatusDate))
Thanks for the help Bob

Thanks Allen I changed "Finished" to "Active" that is giving every horse
in
Finished Mode a Date next to its name, My Combo box on the form is showing
HorseID with their date if they are in finished mode, how would I filter
it so as it only shows the data relevant to HorseID on that form
.....Thanks ...Bob, StatusDate was missing [

Bob, there is a problem here with data types.

If you open tblHorseInfo in design veiw, and choose the StatusDate field,
it is a Date/Time type? If so, the word "Finished" is not a valid value
for the field.

It is possible to create a query that has the word "Finished" in among
the
date fields. However, Access will treat this field as Text. Any criteria
you use won't work right. And if you sort by the field, it will be all
wrong.

The best solution might be to suppress the date if it is finished. A
Date/Time field can contain a Null, so that doesn't mess up the data
type.
To do that, type an expression like this into the Field row of your
query,
in query design:
DisplayDate: CVDate(IIf([tblHorseInfo].[Status] = 'Finished', Null,
[tblHorseInfo].StatusDate]))
(That's all one line.)

That should work unless Status is actually a Number field (typically if
you used the Lookup wizard.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to add this to my Query: if [tblHorseInfo,Status] shows
"Finished" to show "Finished" In [TableHorseInfo,StatusDate]

SELECT tblHorseInfo.HorseID, tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True) AS Expr1,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Expr2,
tblHorseInfo.Invocing, tblHorseInfo.StatusDate
FROM tblHorseInfo
ORDER BY tblHorseInfo.Status, funGetHorse(0,tblHorseInfo.HorseID,True);

Thanks for any help on this...............Bob

 

[Allen Browne]

You want today's date instead of the blank?

CVDate(IIf(tblHorseInfo.Status='Active', Date(),
tblHorseInfo.StatusDate))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Does this seem ok to you it is working, except it is not putting in todays
date

=NZ(DLookUp("DisplayDate","qryFinishedDate","HorseID =" &
tbHorseID.Value),"")

Can this code be altered to enter todays date?
DisplayDate:
CVDate(IIf(tblHorseInfo.Status='Active',Null,tblHorseInfo.StatusDate))
Thanks for the help Bob

Thanks Allen I changed "Finished" to "Active" that is giving every horse
in
Finished Mode a Date next to its name, My Combo box on the form is
showing
HorseID with their date if they are in finished mode, how would I filter
it so as it only shows the data relevant to HorseID on that form
.....Thanks ...Bob, StatusDate was missing [

Bob, there is a problem here with data types.

If you open tblHorseInfo in design veiw, and choose the StatusDate
field,
it is a Date/Time type? If so, the word "Finished" is not a valid value
for the field.

It is possible to create a query that has the word "Finished" in among
the
date fields. However, Access will treat this field as Text. Any criteria
you use won't work right. And if you sort by the field, it will be all
wrong.

The best solution might be to suppress the date if it is finished. A
Date/Time field can contain a Null, so that doesn't mess up the data
type.
To do that, type an expression like this into the Field row of your
query,
in query design:
DisplayDate: CVDate(IIf([tblHorseInfo].[Status] = 'Finished', Null,
[tblHorseInfo].StatusDate]))
(That's all one line.)

That should work unless Status is actually a Number field (typically if
you used the Lookup wizard.)

I want to add this to my Query: if [tblHorseInfo,Status] shows
"Finished" to show "Finished" In [TableHorseInfo,StatusDate]

SELECT tblHorseInfo.HorseID, tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True) AS Expr1,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Expr2,
tblHorseInfo.Invocing, tblHorseInfo.StatusDate
FROM tblHorseInfo
ORDER BY tblHorseInfo.Status, funGetHorse(0,tblHorseInfo.HorseID,True);

 

[Allen Browne]

To filter the form so that it shows only the horses that have Active in the
Status field:

1. Create a query using your table.

2. In the Criteria row under the Status field, enter:
'Active'

3. Save the query.

4. Open your form in design view.

5. Set the RecordSource property of the form to the name of the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen I changed "Finished" to "Active" that is giving every horse
in
Finished Mode a Date next to its name, My Combo box on the form is showing
HorseID with their date if they are in finished mode, how would I filter
it so as it only shows the data relevant to HorseID on that form
.....Thanks ...Bob, StatusDate was missing [

Bob, there is a problem here with data types.

If you open tblHorseInfo in design veiw, and choose the StatusDate field,
it is a Date/Time type? If so, the word "Finished" is not a valid value
for the field.

It is possible to create a query that has the word "Finished" in among
the
date fields. However, Access will treat this field as Text. Any criteria
you use won't work right. And if you sort by the field, it will be all
wrong.

The best solution might be to suppress the date if it is finished. A
Date/Time field can contain a Null, so that doesn't mess up the data
type.
To do that, type an expression like this into the Field row of your
query,
in query design:
DisplayDate: CVDate(IIf([tblHorseInfo].[Status] = 'Finished', Null,
[tblHorseInfo].StatusDate]))
(That's all one line.)

That should work unless Status is actually a Number field (typically if
you used the Lookup wizard.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to add this to my Query: if [tblHorseInfo,Status] shows
"Finished" to show "Finished" In [TableHorseInfo,StatusDate]

SELECT tblHorseInfo.HorseID, tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True) AS Expr1,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Expr2,
tblHorseInfo.Invocing, tblHorseInfo.StatusDate
FROM tblHorseInfo
ORDER BY tblHorseInfo.Status, funGetHorse(0,tblHorseInfo.HorseID,True);

Thanks for any help on this...............Bob

 

[Bob]

Thanks Allen but I wanted the Date for records that are "Finished" and Blank
for "Active" , Date being when it was changed to Finished because the
records come as "Active" Default,.......Thanx Bob

You want today's date instead of the blank?

CVDate(IIf(tblHorseInfo.Status='Active', Date(),
tblHorseInfo.StatusDate))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Does this seem ok to you it is working, except it is not putting in
todays date

=NZ(DLookUp("DisplayDate","qryFinishedDate","HorseID =" &
tbHorseID.Value),"")

Can this code be altered to enter todays date?
DisplayDate:
CVDate(IIf(tblHorseInfo.Status='Active',Null,tblHorseInfo.StatusDate))
Thanks for the help Bob

Thanks Allen I changed "Finished" to "Active" that is giving every horse
in
Finished Mode a Date next to its name, My Combo box on the form is
showing
HorseID with their date if they are in finished mode, how would I filter
it so as it only shows the data relevant to HorseID on that form
.....Thanks ...Bob, StatusDate was missing [
Bob, there is a problem here with data types.

If you open tblHorseInfo in design veiw, and choose the StatusDate
field,
it is a Date/Time type? If so, the word "Finished" is not a valid value
for the field.

It is possible to create a query that has the word "Finished" in among
the
date fields. However, Access will treat this field as Text. Any
criteria
you use won't work right. And if you sort by the field, it will be all
wrong.

The best solution might be to suppress the date if it is finished. A
Date/Time field can contain a Null, so that doesn't mess up the data
type.
To do that, type an expression like this into the Field row of your
query,
in query design:
DisplayDate: CVDate(IIf([tblHorseInfo].[Status] = 'Finished', Null,
[tblHorseInfo].StatusDate]))
(That's all one line.)

That should work unless Status is actually a Number field (typically if
you used the Lookup wizard.)

I want to add this to my Query: if [tblHorseInfo,Status] shows
"Finished" to show "Finished" In [TableHorseInfo,StatusDate]

SELECT tblHorseInfo.HorseID, tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True) AS Expr1,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Expr2,
tblHorseInfo.Invocing, tblHorseInfo.StatusDate
FROM tblHorseInfo
ORDER BY tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True);

 

[Allen Browne]

So if the Status field is Active, you want Blank and for all other cases,
you want it to show StatusDate?

That's what the previous reply did (one before the last one.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen but I wanted the Date for records that are "Finished" and
Blank for "Active" , Date being when it was changed to Finished because
the records come as "Active" Default,.......Thanx Bob

You want today's date instead of the blank?

CVDate(IIf(tblHorseInfo.Status='Active', Date(),
tblHorseInfo.StatusDate))

Does this seem ok to you it is working, except it is not putting in
todays date

=NZ(DLookUp("DisplayDate","qryFinishedDate","HorseID =" &
tbHorseID.Value),"")

Can this code be altered to enter todays date?
DisplayDate:
CVDate(IIf(tblHorseInfo.Status='Active',Null,tblHorseInfo.StatusDate))
Thanks for the help Bob

Thanks Allen I changed "Finished" to "Active" that is giving every
horse
in
Finished Mode a Date next to its name, My Combo box on the form is
showing
HorseID with their date if they are in finished mode, how would I
filter
it so as it only shows the data relevant to HorseID on that form
.....Thanks ...Bob, StatusDate was missing [
Bob, there is a problem here with data types.

If you open tblHorseInfo in design veiw, and choose the StatusDate
field,
it is a Date/Time type? If so, the word "Finished" is not a valid
value
for the field.

It is possible to create a query that has the word "Finished" in among
the
date fields. However, Access will treat this field as Text. Any
criteria
you use won't work right. And if you sort by the field, it will be all
wrong.

The best solution might be to suppress the date if it is finished. A
Date/Time field can contain a Null, so that doesn't mess up the data
type.
To do that, type an expression like this into the Field row of your
query,
in query design:
DisplayDate: CVDate(IIf([tblHorseInfo].[Status] = 'Finished', Null,
[tblHorseInfo].StatusDate]))
(That's all one line.)

That should work unless Status is actually a Number field (typically
if
you used the Lookup wizard.)

I want to add this to my Query: if [tblHorseInfo,Status] shows
"Finished" to show "Finished" In [TableHorseInfo,StatusDate]

SELECT tblHorseInfo.HorseID, tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True) AS Expr1,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Expr2,
tblHorseInfo.Invocing, tblHorseInfo.StatusDate
FROM tblHorseInfo
ORDER BY tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True);

 

[Bob]

Ok went back to that previous code and it is putting in the wrong date
that's why I was getting confused, today my computer says 29th when I put
the record in finished its giving me 28th, never mins one day is no big deal
but how come???....thanks Bob.. yesterday it was showing 27th that's why I
asked for today's date, but its just 24 hours behind LOL...Thanks Bob

Bob, there is a problem here with data types.

If you open tblHorseInfo in design veiw, and choose the StatusDate field,
it is a Date/Time type? If so, the word "Finished" is not a valid value
for the field.

It is possible to create a query that has the word "Finished" in among the
date fields. However, Access will treat this field as Text. Any criteria
you use won't work right. And if you sort by the field, it will be all
wrong.

The best solution might be to suppress the date if it is finished. A
Date/Time field can contain a Null, so that doesn't mess up the data type.
To do that, type an expression like this into the Field row of your query,
in query design:
DisplayDate: CVDate(IIf([tblHorseInfo].[Status] = 'Finished', Null,
[tblHorseInfo].StatusDate]))
(That's all one line.)

That should work unless Status is actually a Number field (typically if
you used the Lookup wizard.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to add this to my Query: if [tblHorseInfo,Status] shows
"Finished" to show "Finished" In [TableHorseInfo,StatusDate]

SELECT tblHorseInfo.HorseID, tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True) AS Expr1,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Expr2,
tblHorseInfo.Invocing, tblHorseInfo.StatusDate
FROM tblHorseInfo
ORDER BY tblHorseInfo.Status, funGetHorse(0,tblHorseInfo.HorseID,True);

Thanks for any help on this...............Bob

 

[Bob]

Ok went back to that previous code and it is putting in the wrong date
that's why I was getting confused, today my computer says 29th when I put
the record in finished its giving me 28th, never ***mind*** one day is no
big deal
but how come???....thanks Bob.. yesterday it was showing 27th that's why I
asked for today's date, but its just 24 hours behind LOL...Thanks Bob

Ok went back to that previous code and it is putting in the wrong date
that's why I was getting confused, today my computer says 29th when I put
the record in finished its giving me 28th, never mins one day is no big
deal but how come???....thanks Bob.. yesterday it was showing 27th that's
why I asked for today's date, but its just 24 hours behind LOL...Thanks
Bob

Bob, there is a problem here with data types.

If you open tblHorseInfo in design veiw, and choose the StatusDate field,
it is a Date/Time type? If so, the word "Finished" is not a valid value
for the field.

It is possible to create a query that has the word "Finished" in among
the date fields. However, Access will treat this field as Text. Any
criteria you use won't work right. And if you sort by the field, it will
be all wrong.

The best solution might be to suppress the date if it is finished. A
Date/Time field can contain a Null, so that doesn't mess up the data
type. To do that, type an expression like this into the Field row of your
query, in query design:
DisplayDate: CVDate(IIf([tblHorseInfo].[Status] = 'Finished', Null,
[tblHorseInfo].StatusDate]))
(That's all one line.)

That should work unless Status is actually a Number field (typically if
you used the Lookup wizard.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to add this to my Query: if [tblHorseInfo,Status] shows
"Finished" to show "Finished" In [TableHorseInfo,StatusDate]

SELECT tblHorseInfo.HorseID, tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True) AS Expr1,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Expr2,
tblHorseInfo.Invocing, tblHorseInfo.StatusDate
FROM tblHorseInfo
ORDER BY tblHorseInfo.Status, funGetHorse(0,tblHorseInfo.HorseID,True);

Thanks for any help on this...............Bob

 

[Allen Browne]

Could that be because the StatusDate field actually contains yesterday's
date?