How to combine values from 2 fields for a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to build an expression in a query that will show dates that are
greater than the due date in one field and is null in the second field which
is the out date. We are putting this expression in the criteria of the out
date field. Can anyone help me?
 
I am not really sure what you need, but as far as the query is concerned, try:
WHERE [due date] < #show date# AND [second field] = #12:00:00 AM#

Hopy this helps some.

John H W
 
What I am trying to do is make a report that shows when out dates are later
than thier due dates plus when the out dates are left blank. I need a query
that will pull both the dates greater than the due date, and those that are
null. Does that help?

John H W said:
I am not really sure what you need, but as far as the query is concerned, try:
WHERE [due date] < #show date# AND [second field] = #12:00:00 AM#

Hopy this helps some.

John H W

donnadele said:
I am trying to build an expression in a query that will show dates that are
greater than the due date in one field and is null in the second field which
is the out date. We are putting this expression in the criteria of the out
date field. Can anyone help me?
Click to expand...
Click to expand...
 
Help please anyone!!! We are getting desperate here. OUr company president
wants this report and we all feel rather like idiots that we can't figure out
how to make this work.

I have two fields, one is "due" and the next is "out". What we want to do
is have a query that pulls the records from the "out" field that are greater
than the "due" field, plus we want to include all the "out" records that are
null. Can this be done?

donnadele said:
What I am trying to do is make a report that shows when out dates are later
than thier due dates plus when the out dates are left blank. I need a query
that will pull both the dates greater than the due date, and those that are
null. Does that help?

John H W said:
I am not really sure what you need, but as far as the query is concerned, try:
WHERE [due date] < #show date# AND [second field] = #12:00:00 AM#

Hopy this helps some.

John H W

donnadele said:
I am trying to build an expression in a query that will show dates that are
greater than the due date in one field and is null in the second field which
is the out date. We are putting this expression in the criteria of the out
date field. Can anyone help me?
Click to expand...
Click to expand...
Click to expand...
 
Here is criteria that should work, although it will be slow.

Field: Out
Criteria: > CDate(NZ([Due],#12/1/1900#))

You could try

Field: Out
CriteriaLine1: > Due


Field: Due
CriteriaLine2: Is Null

In SQL

WHERE Out>Due or Due Is Null
Help please anyone!!! We are getting desperate here. OUr company president
wants this report and we all feel rather like idiots that we can't figure out
how to make this work.

I have two fields, one is "due" and the next is "out". What we want to do
is have a query that pulls the records from the "out" field that are greater
than the "due" field, plus we want to include all the "out" records that are
null. Can this be done?

donnadele said:
What I am trying to do is make a report that shows when out dates are later
than thier due dates plus when the out dates are left blank. I need a query
that will pull both the dates greater than the due date, and those that are
null. Does that help?

John H W said:
I am not really sure what you need, but as far as the query is concerned, try:
WHERE [due date] < #show date# AND [second field] = #12:00:00 AM#

Hopy this helps some.

John H W

:

I am trying to build an expression in a query that will show dates that are
greater than the due date in one field and is null in the second field which
is the out date. We are putting this expression in the criteria of the out
date field. Can anyone help me?
Click to expand...
Click to expand...
Click to expand...
 
I get an error that says Data type mismatch in criteria expression when I try
these.

John Spencer (MVP) said:
Here is criteria that should work, although it will be slow.

Field: Out
Criteria: > CDate(NZ([Due],#12/1/1900#))

You could try

Field: Out
CriteriaLine1: > Due


Field: Due
CriteriaLine2: Is Null

In SQL

WHERE Out>Due or Due Is Null
Help please anyone!!! We are getting desperate here. OUr company president
wants this report and we all feel rather like idiots that we can't figure out
how to make this work.

I have two fields, one is "due" and the next is "out". What we want to do
is have a query that pulls the records from the "out" field that are greater
than the "due" field, plus we want to include all the "out" records that are
null. Can this be done?

donnadele said:
What I am trying to do is make a report that shows when out dates are later
than thier due dates plus when the out dates are left blank. I need a query
that will pull both the dates greater than the due date, and those that are
null. Does that help?

:

I am not really sure what you need, but as far as the query is concerned, try:
WHERE [due date] < #show date# AND [second field] = #12:00:00 AM#

Hopy this helps some.

John H W

:

I am trying to build an expression in a query that will show dates that are
greater than the due date in one field and is null in the second field which
is the out date. We are putting this expression in the criteria of the out
date field. Can anyone help me?
Click to expand...
Click to expand...
Click to expand...
 
In that case, I would guess that your fields are not DateTime fields, but are
actually text fields.

Can you check and see what type of fields Due and Out (what are the real names
by the way) are?
I get an error that says Data type mismatch in criteria expression when I try
these.

John Spencer (MVP) said:
Here is criteria that should work, although it will be slow.

Field: Out
Criteria: > CDate(NZ([Due],#12/1/1900#))

You could try

Field: Out
CriteriaLine1: > Due


Field: Due
CriteriaLine2: Is Null

In SQL

WHERE Out>Due or Due Is Null
Help please anyone!!! We are getting desperate here. OUr company president
wants this report and we all feel rather like idiots that we can't figure out
how to make this work.

I have two fields, one is "due" and the next is "out". What we want to do
is have a query that pulls the records from the "out" field that are greater
than the "due" field, plus we want to include all the "out" records that are
null. Can this be done?

:

What I am trying to do is make a report that shows when out dates are later
than thier due dates plus when the out dates are left blank. I need a query
that will pull both the dates greater than the due date, and those that are
null. Does that help?

:

I am not really sure what you need, but as far as the query is concerned, try:
WHERE [due date] < #show date# AND [second field] = #12:00:00 AM#

Hopy this helps some.

John H W

:

I am trying to build an expression in a query that will show dates that are
greater than the due date in one field and is null in the second field which
is the out date. We are putting this expression in the criteria of the out
date field. Can anyone help me?
Click to expand...
Click to expand...
Click to expand...
 
Due is Text and Out is Date Time. Sometimes they put "ASAP" in the Due
field, so I had to make it Text. I really appreciate this. I'm spending
way to much time trying to get this to work. Thank you.

John Spencer (MVP) said:
In that case, I would guess that your fields are not DateTime fields, but are
actually text fields.

Can you check and see what type of fields Due and Out (what are the real names
by the way) are?
I get an error that says Data type mismatch in criteria expression when I try
these.

John Spencer (MVP) said:
Here is criteria that should work, although it will be slow.

Field: Out
Criteria: > CDate(NZ([Due],#12/1/1900#))

You could try

Field: Out
CriteriaLine1: > Due


Field: Due
CriteriaLine2: Is Null

In SQL

WHERE Out>Due or Due Is Null

donnadele wrote:

Help please anyone!!! We are getting desperate here. OUr company president
wants this report and we all feel rather like idiots that we can't figure out
how to make this work.

I have two fields, one is "due" and the next is "out". What we want to do
is have a query that pulls the records from the "out" field that are greater
than the "due" field, plus we want to include all the "out" records that are
null. Can this be done?

:

What I am trying to do is make a report that shows when out dates are later
than thier due dates plus when the out dates are left blank. I need a query
that will pull both the dates greater than the due date, and those that are
null. Does that help?

:

I am not really sure what you need, but as far as the query is concerned, try:
WHERE [due date] < #show date# AND [second field] = #12:00:00 AM#

Hopy this helps some.

John H W

:

I am trying to build an expression in a query that will show dates that are
greater than the due date in one field and is null in the second field which
is the out date. We are putting this expression in the criteria of the out
date field. Can anyone help me?
Click to expand...
Click to expand...
Click to expand...
 
Due and Out are the real names of the fields. I always use the keep it
simple stupid method on these things. Since I've forgotten so much of what I
learned in my access classes, it's a good thing.

John Spencer (MVP) said:
In that case, I would guess that your fields are not DateTime fields, but are
actually text fields.

Can you check and see what type of fields Due and Out (what are the real names
by the way) are?
I get an error that says Data type mismatch in criteria expression when I try
these.

John Spencer (MVP) said:
Here is criteria that should work, although it will be slow.

Field: Out
Criteria: > CDate(NZ([Due],#12/1/1900#))

You could try

Field: Out
CriteriaLine1: > Due


Field: Due
CriteriaLine2: Is Null

In SQL

WHERE Out>Due or Due Is Null

donnadele wrote:

Help please anyone!!! We are getting desperate here. OUr company president
wants this report and we all feel rather like idiots that we can't figure out
how to make this work.

I have two fields, one is "due" and the next is "out". What we want to do
is have a query that pulls the records from the "out" field that are greater
than the "due" field, plus we want to include all the "out" records that are
null. Can this be done?

:

What I am trying to do is make a report that shows when out dates are later
than thier due dates plus when the out dates are left blank. I need a query
that will pull both the dates greater than the due date, and those that are
null. Does that help?

:

I am not really sure what you need, but as far as the query is concerned, try:
WHERE [due date] < #show date# AND [second field] = #12:00:00 AM#

Hopy this helps some.

John H W

:

I am trying to build an expression in a query that will show dates that are
greater than the due date in one field and is null in the second field which
is the out date. We are putting this expression in the criteria of the out
date field. Can anyone help me?
Click to expand...
Click to expand...
Click to expand...
 
Well, that is the problem. You cannot really compare two different types of
things. You might try forcing ASAP to some date value such as today's date.

Field: Out
Criteria: > IIF(Due="ASAP" or Due is Null,Date(),CDate([Due]))



Due is Text and Out is Date Time. Sometimes they put "ASAP" in the Due
field, so I had to make it Text. I really appreciate this. I'm spending
way to much time trying to get this to work. Thank you.

John Spencer (MVP) said:
In that case, I would guess that your fields are not DateTime fields, but are
actually text fields.

Can you check and see what type of fields Due and Out (what are the real names
by the way) are?
I get an error that says Data type mismatch in criteria expression when I try
these.

:

Here is criteria that should work, although it will be slow.

Field: Out
Criteria: > CDate(NZ([Due],#12/1/1900#))

You could try

Field: Out
CriteriaLine1: > Due


Field: Due
CriteriaLine2: Is Null

In SQL

WHERE Out>Due or Due Is Null

donnadele wrote:

Help please anyone!!! We are getting desperate here. OUr company president
wants this report and we all feel rather like idiots that we can't figure out
how to make this work.

I have two fields, one is "due" and the next is "out". What we want to do
is have a query that pulls the records from the "out" field that are greater
than the "due" field, plus we want to include all the "out" records that are
null. Can this be done?

:

What I am trying to do is make a report that shows when out dates are later
than thier due dates plus when the out dates are left blank. I need a query
that will pull both the dates greater than the due date, and those that are
null. Does that help?

:

I am not really sure what you need, but as far as the query is concerned, try:
WHERE [due date] < #show date# AND [second field] = #12:00:00 AM#

Hopy this helps some.

John H W

:

I am trying to build an expression in a query that will show dates that are
greater than the due date in one field and is null in the second field which
is the out date. We are putting this expression in the criteria of the out
date field. Can anyone help me?
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much, once I changed the Due field to Date/Time it worked. I
appreciate all of your help, to both of you.

John Spencer (MVP) said:
In that case, I would guess that your fields are not DateTime fields, but are
actually text fields.

Can you check and see what type of fields Due and Out (what are the real names
by the way) are?
I get an error that says Data type mismatch in criteria expression when I try
these.

John Spencer (MVP) said:
Here is criteria that should work, although it will be slow.

Field: Out
Criteria: > CDate(NZ([Due],#12/1/1900#))

You could try

Field: Out
CriteriaLine1: > Due


Field: Due
CriteriaLine2: Is Null

In SQL

WHERE Out>Due or Due Is Null

donnadele wrote:

Help please anyone!!! We are getting desperate here. OUr company president
wants this report and we all feel rather like idiots that we can't figure out
how to make this work.

I have two fields, one is "due" and the next is "out". What we want to do
is have a query that pulls the records from the "out" field that are greater
than the "due" field, plus we want to include all the "out" records that are
null. Can this be done?

:

What I am trying to do is make a report that shows when out dates are later
than thier due dates plus when the out dates are left blank. I need a query
that will pull both the dates greater than the due date, and those that are
null. Does that help?

:

I am not really sure what you need, but as far as the query is concerned, try:
WHERE [due date] < #show date# AND [second field] = #12:00:00 AM#

Hopy this helps some.

John H W

:

I am trying to build an expression in a query that will show dates that are
greater than the due date in one field and is null in the second field which
is the out date. We are putting this expression in the criteria of the out
date field. Can anyone help me?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top