Using a Union query

[Joan]

When using a union query to join similar tables, do the data types of each
field in one table have to be the same as the data types for the
corresponding field in the other table?

I have a LitterShot subform where the user wants to be able to enter two
dates ( if needed) in the [Date Given] field separated by a comma. Right now
the data type of this field is Date. These dates would not be consecutive
and would indicate if the same vaccine was given to the same dog on more
than one date. Right now, I have the form and table structure set up so that
the user must record a separate littershot entry in this instance. The user
indicates that this would save them time if they could just record both
dates in the same textbox.

After looking through my reports and queries, I think that I might be able
to allow this for the LitterShots table because the [Date Given] field is
not used in any calculations or functions. I would just change the data type
to text or memo type for LitterShots.[Date Given]. However there is the
problem of this union query where I join the LitterShots table to the
DogShots table. (DogShots are given by the broker and Littershots are given
by the breeder. and are recorded in a different manner). The [Date Given]
field in the DogShots table is used in a calculation in a query where I must
find the MAX of the [Date Given] field. I would not be able to change the
data type of DogShots.[Date Given] to a text field for this reason.

Any advice regarding this would be appreciated.

Joan

 

[Ken Snell]

Yes, the data types/formats must be the same.

For your union query, don't use the actual table with the Date field in it;
instead, create a query that will convert the Date field into a text field
and then use that query as one part of the union query.

 

[Joan]

Ken,
Thanks for replying to my post. How do I convert the Date field into a text
field in a query? I've tried clicking on the field selector of the [Date
Given] field in my query and then right clicking and selecting 'Properties'
from the drop down menu. The format property of this field only has date
and time formats to choose from. I don't see where I can convert the Date
field into a text field?

Joan

Yes, the data types/formats must be the same.

For your union query, don't use the actual table with the Date field in it;
instead, create a query that will convert the Date field into a text field
and then use that query as one part of the union query.

--
Ken Snell
<MS ACCESS MVP>

When using a union query to join similar tables, do the data types of each
field in one table have to be the same as the data types for the
corresponding field in the other table?

I have a LitterShot subform where the user wants to be able to enter two
dates ( if needed) in the [Date Given] field separated by a comma. Right now
the data type of this field is Date. These dates would not be consecutive
and would indicate if the same vaccine was given to the same dog on more
than one date. Right now, I have the form and table structure set up so that
the user must record a separate littershot entry in this instance. The user
indicates that this would save them time if they could just record both
dates in the same textbox.

After looking through my reports and queries, I think that I might be able
to allow this for the LitterShots table because the [Date Given] field is
not used in any calculations or functions. I would just change the data type
to text or memo type for LitterShots.[Date Given]. However there is the
problem of this union query where I join the LitterShots table to the
DogShots table. (DogShots are given by the broker and Littershots are given
by the breeder. and are recorded in a different manner). The [Date Given]
field in the DogShots table is used in a calculation in a query where I must
find the MAX of the [Date Given] field. I would not be able to change the
data type of DogShots.[Date Given] to a text field for this reason.

Any advice regarding this would be appreciated.

Joan

 

[Ken Snell]

You use the Format function in a calculated field; for example:

TextDate: Format([DateFieldName], "mm/dd/yyyy")


--
Ken Snell
<MS ACCESS MVP>

Ken,
Thanks for replying to my post. How do I convert the Date field into a text
field in a query? I've tried clicking on the field selector of the [Date
Given] field in my query and then right clicking and selecting 'Properties'
from the drop down menu. The format property of this field only has date
and time formats to choose from. I don't see where I can convert the Date
field into a text field?

Joan

Yes, the data types/formats must be the same.

For your union query, don't use the actual table with the Date field in it;
instead, create a query that will convert the Date field into a text field
and then use that query as one part of the union query.

--
Ken Snell
<MS ACCESS MVP>

When using a union query to join similar tables, do the data types of each
field in one table have to be the same as the data types for the
corresponding field in the other table?

I have a LitterShot subform where the user wants to be able to enter two
dates ( if needed) in the [Date Given] field separated by a comma.

Right
now

the data type of this field is Date. These dates would not be consecutive
and would indicate if the same vaccine was given to the same dog on more
than one date. Right now, I have the form and table structure set up

so
that

the user must record a separate littershot entry in this instance. The user
indicates that this would save them time if they could just record both
dates in the same textbox.

After looking through my reports and queries, I think that I might be able
to allow this for the LitterShots table because the [Date Given] field is
not used in any calculations or functions. I would just change the

data
type

to text or memo type for LitterShots.[Date Given]. However there is the
problem of this union query where I join the LitterShots table to the
DogShots table. (DogShots are given by the broker and Littershots are given
by the breeder. and are recorded in a different manner). The [Date Given]
field in the DogShots table is used in a calculation in a query where

I
must

find the MAX of the [Date Given] field. I would not be able to change the
data type of DogShots.[Date Given] to a text field for this reason.

Any advice regarding this would be appreciated.

Joan

 

[Joan]

Thanks, Ken.
I used the Format function in a calculated field like you suggested and
everything now works great. The union query works and I could still perform
calculations on DogShots.[DateGiven] in a different query. Thanks again.

Joan

You use the Format function in a calculated field; for example:

TextDate: Format([DateFieldName], "mm/dd/yyyy")


--
Ken Snell
<MS ACCESS MVP>

Ken,
Thanks for replying to my post. How do I convert the Date field into a text
field in a query? I've tried clicking on the field selector of the [Date
Given] field in my query and then right clicking and selecting 'Properties'
from the drop down menu. The format property of this field only has date
and time formats to choose from. I don't see where I can convert the Date
field into a text field?

Joan

Yes, the data types/formats must be the same.

For your union query, don't use the actual table with the Date field

in
it;

instead, create a query that will convert the Date field into a text field
and then use that query as one part of the union query.

--
Ken Snell
<MS ACCESS MVP>

When using a union query to join similar tables, do the data types

of
each

field in one table have to be the same as the data types for the
corresponding field in the other table?

I have a LitterShot subform where the user wants to be able to enter two
dates ( if needed) in the [Date Given] field separated by a comma. Right
now
the data type of this field is Date. These dates would not be consecutive
and would indicate if the same vaccine was given to the same dog on more
than one date. Right now, I have the form and table structure set up so
that
the user must record a separate littershot entry in this instance. The
user
indicates that this would save them time if they could just record both
dates in the same textbox.

After looking through my reports and queries, I think that I might

be
able

to allow this for the LitterShots table because the [Date Given]

field
is

not used in any calculations or functions. I would just change the data
type
to text or memo type for LitterShots.[Date Given]. However there is the
problem of this union query where I join the LitterShots table to the
DogShots table. (DogShots are given by the broker and Littershots are
given
by the breeder. and are recorded in a different manner). The [Date Given]
field in the DogShots table is used in a calculation in a query

where

I

must
find the MAX of the [Date Given] field. I would not be able to

change
the

data type of DogShots.[Date Given] to a text field for this reason.

Any advice regarding this would be appreciated.

Joan