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  1692  
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 thinking that using:
docmd.openfrm "frmName",,,where "=" & cboSelectID
will open all records and then just navigate to that filtered record,
which is not as fast/efficient as using a query where the criteria is
|
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 value for from a combo
box. In my query I set the criteria for to
... My query finds the proper values
for .
Now I also want to find the values if I select a value for in
a separate combo box. In both controls, OnChange, I set the value of
|
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, Students.LastNM, Students.FirstNM,
Students.MI, Students.DOB, Students.GenderCD, Students.EthnicityCD,
Students.EligibilityCD, Students.UBInitiative, Students.NCESSchID,
Students.ProjEntryDT, Students.ProjReEntDT, Students.LastSerDT,
Students.Reason,...
|
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
controls on the form and uses them a the criteria for changing the
query definition that the form is based on - so far so good.
|
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 key)
ss#
| | |
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 display the data
to the user. If that form is already open, how do I make it refresh its
data source and display the new data.
Here is what I am doing.
>From a text entry form, I create an SQL query dymanically and alter the
sql statement of an...
|
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 making
this work...
|
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 form is based on a Select
Query. I managed to alter that query so that the field shows up in the field
list.
My question is, why exactly does access base this form on a query? What is
the difference and the benefit?
Thanks,
john
|
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 the user has to find his record to add or modify existing data. Originally, I built the form based on a query with an input criteria for the last name and first name. The Last_Name critera is Like &"*" so that partial names will work. The...
|
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 I enter the zip code in the
form, I'd like the Salesman ID field in tblLeadInfo to fill in
accordingly and also show the salesman name on the form.
I'm sure this is simple, I just can't seem to get it straight in my
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
