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?
5 1678
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.
-
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?
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.
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!
-
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!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: John |
last post by:
Just a general question...
I'm currently using a combobox that when updated, opens a form with
its recordset based on a query using the combo box value as the
criteria.
I'm I correct in...
|
by: neptune |
last post by:
I have a query where each customer has an or . Sometimes
both fields for a customer are populated, but if is null, then
will be populated and vice versa. I have a form,
, where I select a...
|
by: DebbieG |
last post by:
I have a form based on this query:
SELECT Students.LastSerDT, OtherInfo.Served, OtherInfo.HSGradYr,
OtherInfo.ActivePart, OtherInfo.Served, Students.SSN, & ", " &
& " " & AS Name,...
|
by: dkintheuk |
last post by:
Hi all,
Using Access 2000 on XP Pro PC.
I have a form that is based on a presaved query - all fine with this.
I also have a refresh button that takes the values from various unbound...
|
by: meganrobertson22 |
last post by:
hi everybody-
what is the best way to add data from one form to another?
i have 2 tables: person and contract. here are some of the fields.
table: person
personid (autonumber and primary...
|
by: Robert |
last post by:
I am trying to give the user dynamic search capabilities to select
almost any record in the database from criteria they select. Everything
seems to work except when I open the display form to...
|
by: fstenoughsnoopy |
last post by:
I have a customer order database and I need to pull a customers
information, ie first name, last name, address, city, state, zip,
phone, etc, into the oder table. i don't know how to go about...
|
by: john |
last post by:
I created a form via the wizard with 1 main table and 2 one-on-one tables.
As i couldn't add a field to the form (a field that I added to the table
after creating the form), I googled out that the...
|
by: DWolff |
last post by:
I'm somewhat new to Access, and have no VB experience at all (except for modifying existing code where obvious).
I built a data entry form that works fine. Typically, a client will call in and...
|
by: tess |
last post by:
I have:
table 1 - tblLeadInfo which includes a salesman ID field
table 2 - tbllkpSalesman with all zips in the state and a Salesman
assigned to that area.
I have a form based on table #1 When...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |