If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Output a user entered value
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 |
#2
|
|||
|
|||
Output a user entered value
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. "Andy" wrote: 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 |
#3
|
|||
|
|||
Output a user entered value
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"); "Jerry Whittle" wrote: 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. "Andy" wrote: 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 |
#4
|
|||
|
|||
Output a user entered value
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 '================================================= === Andy wrote: 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"); "Jerry Whittle" wrote: 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. "Andy" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|