Question Locked

Form Based Query

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

2 replies

by tony hopkinson

Replicate('0',2 - DataLength(FiscalYear)) + FiscalYear
Assuming FiscalYear is a varchar or NVarchar

This discussion is closed to new replies.