Output a user entered value

[Andy]

I have a make table query with some dates in it. The user-entered field is
set as a 'where' field where they enter a month end date. I want this entered
value to be written as it's own field within the new table.

The only way that I knew how to do this, would be to enter the value into a
table and then call the value within the table, but did not want to utilise a
table in this instance.

Thanks

 

[Jerry Whittle]

If you are using a parameter, you can put the results of what was typed in a
field.

PARAMETERS [Enter the Date] DateTime;
SELECT Defective.Serial_Number,
Defective.Date,
[Enter the Date] AS [The Date]
INTO tblTestDateParameter
FROM Defective
WHERE (((Defective.Date)=[Enter the Date]));

In the QBE grid I put this in a field:

The Date: [Enter the Date]

It must match the parameter in the Where exactly.

 

[Andy]

Got this to work, but now the date is outputting as a text field and I need
to convert it to a date field?

SELECT t_SQL_TABLE_FINAL.NUTH_DERIVED_SPEC,
t_SQL_TABLE_FINAL.ACTIVITY_TYPE_CD4, t_SQL_TABLE_FINAL.PCT_GROUP_CD,
IIf(t_SQL_TABLE_FINAL!ACTIVITY_TYPE_CD4 In
("NOP","FOP"),"HRG00",t_SQL_TABLE_FINAL!V4_HRG_CD) AS V4_HRG_CD,
Sum(t_SQL_TABLE_FINAL.INSTANCES) AS SumOfINSTANCES,
Sum(t_SQL_TABLE_FINAL.INCOME) AS SumOfINCOME, Format([Last day of reporting
month],"dd/mm/yyyy") AS [Reporting Date] INTO [t_Report A Table]
FROM t_SQL_TABLE_FINAL
WHERE (((t_SQL_TABLE_FINAL.ACTIVITY_DATE)<=[Last day of reporting month]))
GROUP BY t_SQL_TABLE_FINAL.NUTH_DERIVED_SPEC,
t_SQL_TABLE_FINAL.ACTIVITY_TYPE_CD4, t_SQL_TABLE_FINAL.PCT_GROUP_CD,
IIf(t_SQL_TABLE_FINAL!ACTIVITY_TYPE_CD4 In
("NOP","FOP"),"HRG00",t_SQL_TABLE_FINAL!V4_HRG_CD), Format([Last day of
reporting month],"dd/mm/yyyy");

If you are using a parameter, you can put the results of what was typed in a
field.

PARAMETERS [Enter the Date] DateTime;
SELECT Defective.Serial_Number,
Defective.Date,
[Enter the Date] AS [The Date]
INTO tblTestDateParameter
FROM Defective
WHERE (((Defective.Date)=[Enter the Date]));

In the QBE grid I put this in a field:

The Date: [Enter the Date]

It must match the parameter in the Where exactly.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a make table query with some dates in it. The user-entered field is
set as a 'where' field where they enter a month end date. I want this entered
value to be written as it's own field within the new table.

The only way that I knew how to do this, would be to enter the value into a
table and then call the value within the table, but did not want to utilise a
table in this instance.

Thanks

 

[John Spencer]

Then you cannot use the format function in the query. Format turns the
value into a string.

You can always use the format property of the control where you are
displaying the data to see it formatted. The actual value will still be
a date.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Got this to work, but now the date is outputting as a text field and I need
to convert it to a date field?

SELECT t_SQL_TABLE_FINAL.NUTH_DERIVED_SPEC,
t_SQL_TABLE_FINAL.ACTIVITY_TYPE_CD4, t_SQL_TABLE_FINAL.PCT_GROUP_CD,
IIf(t_SQL_TABLE_FINAL!ACTIVITY_TYPE_CD4 In
("NOP","FOP"),"HRG00",t_SQL_TABLE_FINAL!V4_HRG_CD) AS V4_HRG_CD,
Sum(t_SQL_TABLE_FINAL.INSTANCES) AS SumOfINSTANCES,
Sum(t_SQL_TABLE_FINAL.INCOME) AS SumOfINCOME, Format([Last day of reporting
month],"dd/mm/yyyy") AS [Reporting Date] INTO [t_Report A Table]
FROM t_SQL_TABLE_FINAL
WHERE (((t_SQL_TABLE_FINAL.ACTIVITY_DATE)<=[Last day of reporting month]))
GROUP BY t_SQL_TABLE_FINAL.NUTH_DERIVED_SPEC,
t_SQL_TABLE_FINAL.ACTIVITY_TYPE_CD4, t_SQL_TABLE_FINAL.PCT_GROUP_CD,
IIf(t_SQL_TABLE_FINAL!ACTIVITY_TYPE_CD4 In
("NOP","FOP"),"HRG00",t_SQL_TABLE_FINAL!V4_HRG_CD), Format([Last day of
reporting month],"dd/mm/yyyy");

If you are using a parameter, you can put the results of what was typed in a
field.

PARAMETERS [Enter the Date] DateTime;
SELECT Defective.Serial_Number,
Defective.Date,
[Enter the Date] AS [The Date]
INTO tblTestDateParameter
FROM Defective
WHERE (((Defective.Date)=[Enter the Date]));

In the QBE grid I put this in a field:

The Date: [Enter the Date]

It must match the parameter in the Where exactly.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a make table query with some dates in it. The user-entered field is
set as a 'where' field where they enter a month end date. I want this entered
value to be written as it's own field within the new table.

The only way that I knew how to do this, would be to enter the value into a
table and then call the value within the table, but did not want to utilise a
table in this instance.

Thanks