Form Based Query

[iChappy]

I have a query based off of two fields in an Access form:

Fiscal Week (user manual inputs #) & Fiscal Year (combo box defaulted to current year however may select other years)

The query is a where query and the criteria is based on the values of the above listed form fields.

Here is my delima: If a user types 05 into the Fiscal Week field on the form then the query runs properly; however if a 5 is typed then the query does not process as it should.

Needed: for the end result of the number calculated in that field, if less than 10, to be two digits... so a 0 needs to be input at some point.

I have tried: changing to text and formatting with a leading 0, however the query will not work when I do it that way; concatentating a 0, however it seems to process it as + rather than placing a zero on the beginning or end of the number.

What can I do to make the query process correctly but also not worry about the user typing a single digit only (5, instead of 05)... it will be done for them some where along the way?

Is there any type of vba coding or sql statement I can use to accomplish this?

[jaxjagfan]

I have a query based off of two fields in an Access form:

Fiscal Week (user manual inputs #) & Fiscal Year (combo box defaulted to current year however may select other years)

The query is a where query and the criteria is based on the values of the above listed form fields.

Here is my delima: If a user types 05 into the Fiscal Week field on the form then the query runs properly; however if a 5 is typed then the query does not process as it should.

Needed: for the end result of the number calculated in that field, if less than 10, to be two digits... so a 0 needs to be input at some point.

I have tried: changing to text and formatting with a leading 0, however the query will not work when I do it that way; concatentating a 0, however it seems to process it as + rather than placing a zero on the beginning or end of the number.

What can I do to make the query process correctly but also not worry about the user typing a single digit only (5, instead of 05)... it will be done for them some where along the way?

Is there any type of vba coding or sql statement I can use to accomplish this?

Right("00" & [Fiscal Week],2)

Whatever gets entered into [Fiscal Week] will be 2 characters.

This should work for you by putting all entries into a 2 character no matter what your end user enters into [Fiscal Week].

I assumed you have a textbox called "Fiscal Week". If not just change the code snippet.

[iChappy]

Right("00" & [Fiscal Week],2)

Whatever gets entered into [Fiscal Week] will be 2 characters.

This should work for you by putting all entries into a 2 character no matter what your end user enters into [Fiscal Week].

I assumed you have a textbox called "Fiscal Week". If not just change the code snippet.

Yes, good assumption:
Forms![Fiscal Week Form]![Fiscal Week]!

I am new to the coding side of it... would you please provide the full code?

[jaxjagfan]

Yes, good assumption:
Forms![Fiscal Week Form]![Fiscal Week]!

I am new to the coding side of it... would you please provide the full code?

Post the SQL from your query and I will modify and repost.

[iChappy]

Post the SQL from your query and I will modify and repost.

Thank You!!!
It is in the WHERE criteria of the below query that references to the Fiscal Week field in the form. ([Forms]![Fiscal Week Form]![Fiscal Week]

SELECT [Driver QOS Sum Report Detail Query].Driver,
       Avg([Driver QOS Sum Report Detail Query].[Blended QOS]) AS [AvgOfBlended QOS],
       Avg([Driver QOS Sum Report Detail Query].[Total QOS]) AS [AvgOfTotal QOS],
       Avg([Driver QOS Sum Report Detail Query].[Team Back]) AS [AvgOfTeam Back],
       Avg([Driver QOS Sum Report Detail Query].Appearance) AS AvgOfAppearance,
       Avg([Driver QOS Sum Report Detail Query].[Protect Property]) AS [AvgOfProtect Property],
       Count([Driver QOS Sum Report Detail Query].[Total QOS]) AS [CountOfTotal QOS]
FROM [Driver QOS Sum Report Detail Query]
WHERE ((([Driver QOS Sum Report Detail Query].FISCAL_WEEK)>=IIf(([Forms]![Fiscal Week Form]![Fiscal Week]-4)<10,[Forms]![Fiscal Week Form]![Fiscal Year] & ([Forms]![Fiscal Week Form]![Fiscal Week]-4),[Forms]![Fiscal Week Form]![Fiscal Year] & [Forms]![Fiscal Week Form]![Fiscal Week]) And <=[Forms]![Fiscal Week Form]![Fiscal Year] & [Forms]![Fiscal Week Form]![Fiscal Week]))
GROUP BY [Driver QOS Sum Report Detail Query].Driver;

Thank you for your time!

[iChappy]

Right("00" & [Fiscal Week],2)

Whatever gets entered into [Fiscal Week] will be 2 characters.

This should work for you by putting all entries into a 2 character no matter what your end user enters into [Fiscal Week].

I assumed you have a textbox called "Fiscal Week". If not just change the code snippet.

All is working now. Thank you for your help!