Forms :: Conditionally Using Asterisk In A Query Formed From Strings?

Jul 10, 2015

I have a button on a Form which opens a report, passing in two WHERE clauses which are driven by two comboboxes on the Form (one for ProductType and one for ProductCollection).

Everything works great but I needed to add an "All" option to the comboboxes so that the user can get all Producttypes from a specific Collection or vice versa.

I've managed to add the "All" option to one of the comboboxes (using the UNION query method on the rowsource). I'm a bit stumped though, as to how I would actually tweak the function below to do a select * where the combobox value is found to be = All.

Code:
Private Sub ProduceReport_Click()
Dim strWhere As String
strWhere = "producttype = " & Me.ProductTypeForReport
strWhere = strWhere & " AND ProductCollectionName = '" & Me.CollectionForReport
strWhere = strWhere & "'"
Debug.Print strWhere
DoCmd.OpenReport "Products Report", acViewReport, , strWhere
End Sub

View Replies


ADVERTISEMENT

Forms :: Allow Asterisk Or Numbers Only In Field

Jan 16, 2014

I've got a form where the user can only enter the character asterisk or numbers. No letters or other special characters allowed.

The asterisk is a problem since its a wildcard. How do I allow the user to use it or numbers only? I've tried Validation Rules, I've tried vba but I don't seem to manage to get this to work properly.

View 1 Replies View Related

Forms :: Filtered Search Form Using Asterisk As Wildcard Default Value In Combo Boxes

Aug 23, 2013

I'm trying to make a filtered search form using "*" as a wildcard default value in combo boxes, this works for all the text fields except for the account number field (Numeric primary key). After quite a bit of reading up and searching, I tried using the following as the row source;

SELECT customers.ACCOUNT_NO, customers.CUSTOMER FROM customers; UNION SELECT "*", "All" From Customers;

But am still getting "#Error" in the combo box.

View 2 Replies View Related

Forms :: Conditionally Formatting A Drop-down Box

Apr 30, 2014

I work for a doctor, and I am creating a database to assist with surgery scheduling. There are a lot of pieces and parts to this process, and I am building a "bird's eye view" of tasks to be completed, such as "Notified OR," "Sent Surgery Packet," and "Scheduled Post-Op Appt." Each one of these fields has a lookup attached to it with three choices: "Yes," "No," and "Not Needed."

What I want is for the drop-down box with the three choices to be (a) automatically defaulted to "No," (b) immediately turn red if "No" is displayed or immediately turn green if "Yes" or "Not Needed" is displayed. In this way, the goal for our scheduler will be to get all the drop-downs to turn green (an easy visual cue that the patient is ready for surgery) by the date of the procedure.

View 1 Replies View Related

Forms :: Hide Controls When A Form Is Opened Conditionally

Oct 7, 2014

I've searched and can only find information setting the visible property when selections are made on an open form--I want to set the visible property when the form is opened depending on which avenue the user chooses from the navigation form.

I have a navigation form (Form A) and a second form with a subform (Form B). The user will choose from a combobox, either an existing record or a new record on Form A. On Form A there are two buttons, one that will take the user to the correct record on Form B for editing and one that will open to a new record on Form B. Once Form B is open, all controls will be blank (if a new record is chosen) or with certain controls prepopulated if an existing record is chosen.

What I want to do is to hide controls on the main form (not the subform) of Form B if the user chooses an existing record. Form A's buttons work correctly to open Form B right now. I want to be able to hide prepopulated controls on Form B if the user chooses an existing record from Form A.

Here's the code I'm using to open Form B to an existing record. I'd like to set the visible property here if at all possible.

NOTE: "Form A" and "Form B" are not the actual names of my forms--it's just easier for illustration purposes here in my question.

Private Sub cmdAddLogEntry_Click()
DoCmd.OpenForm "Form B", acNormal, , "Activity_ID = " & Me.cboINum
DoCmd.Close acForm, "Form A", acSaveYes

End Sub

View 4 Replies View Related

Conditionally Select Query Fields

May 10, 2007

Is there a way to select a query field based on conditions? There is a table at work that I need to use for my reporting in which the field names are the months of the year. Since my report is monthly for that specific month only, I have to change my field in the query every single month. I would like to be able to put in some type of code that will automatically select the proper month field.

Is this possible?

Thanks! :)

View 2 Replies View Related

Forms :: Conditionally Change Back Color Based On Value Of A Control

Mar 15, 2014

How do I conditionally change the Back Color and Alternate Back Color based on the value of notes.FollowUpDate? I have a continuous form using the following code:

Code:
Private Sub b_all_past_Click()
Me.resultsFrame.SourceObject = "FollowUp_bystaff"
Me.resultsFrame.Form.RecordSource = "SELECT * From notes WHERE (((Notes.FollowUpDate) < Date()+1) And ((Notes.followup_person_id) = GetCurrentUserId()))ORDER BY notes.priority,(notes.FollowUpDate) DESC;" '
End Sub

View 1 Replies View Related

Strings Within Strings Using Expression Builder

Jun 6, 2007

Hello

I have two tables with names of people in different forms.

table1 : [name] can be either in the form of :
- firstname & firstname surname
- surname, firstname

table2: [surname]

I'm trying to select all the records in table1 where [table2]![surname] is part of [table1]![name]

I've tried using

Like "*"&[surname]&"*"

This returns everything rather than the similar names.

I think this should be simple but can't work it out.

View 1 Replies View Related

Modules & VBA :: Conditionally Increment Text String Query

Sep 27, 2013

I have a simple select query , and I want to add a field that will take the value from another field and increment it based on a condtion.

i know i need a case statement but not sure how to go about this ..
column 4 is my desired result

i want to increment the text string from column 3 based on column 3 and restart the increment based on column 1

values of A would increment in the following manner 01,11,21,31,41 etc
values of O would increment 02,12,22,32,42,52,62

i'd also like the option that if a new value were introduced to increment it

values of x would increment 80,81,82,83,84

Example data .. apples and oranges ..

column1 column2(name) column3(type) column4
mary braeburn A 01A
mary blood O 02A
tom cortland A 01A
tom fuji A 11A
tom navel O 02O
tom mcintosh A 21A

[Code]...

View 2 Replies View Related

How To Substitute Asterisk In Access?

Apr 19, 2007

I have asterisks "*" in a table in Access
The table is huge and Excel can't deal with it.
I tried ~* but it doesn't work in Access.

So what would you do?

View 3 Replies View Related

Searching For The Asterisk Character

Oct 24, 2005

Someone used the asterisk (actually, 2 of them **) character in a field to 'mark' it for querying. Since it is a wildcard, how do I query the field to show only the records with "**" in it? What is the best way to remove them using an update query???

Thanks in advance for ANY help.

View 1 Replies View Related

Search For And Remove An Asterisk?

Nov 3, 2006

Is there a way in a query to search for an asterisk (*) in a field and if one is found, to then remove it?

I'm aware the asterisk is a wildcard symbol in Access, which is why I'm asking what I'm asking. This dang file I received has data in a field and the last character can be an asterisk at times.

Thanks so much in advance!! Looking forward toyour replies!!!!

View 5 Replies View Related

Query To Concatenate Two Strings

Sep 21, 2011

I have written a query to concat two strings,

select concat([first_name] & ', ' & [last_name]) as [Employee Name] from [employee_details]

THe above query is returning the result correctly only if both first_name & last_name fields are not empty. If the fields are empty, it is dispalyingcomma (,).

View 1 Replies View Related

Queries :: Create A Query That Strings Data Horizontally That Corresponds To Primary Key?

Aug 25, 2014

I need to create a query that strings data horizontally that corresponds to the primary key.

Example of what I am looking for

Fields:
Part_No,
Mfg_No,
Mfg_Name

Query Columns: Part_No, Mfg_Info (Mfg_No: Mfg_Name)

Also if the part_no (primary key) has multiple values I need the data to string horizontally like this:

Part_No, Mfg_Info (Mfg_No1: Mfg_Name1),
Mfg_Info (Mfg_No2: Mfg_Name2).......

View 14 Replies View Related

Queries :: Select Query To Pull Records Based On Multiple Strings Entered By A User?

May 1, 2013

Is it possible to run a basic select query to pull records based on multiple strings entered by a user?

I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'

This allows the user to input one MMDBID and the records are retrieved from the db.

I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.

But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above

SQL statement (Like) is below :

SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));

View 10 Replies View Related

Conditionally Numbered Sequence

Aug 29, 2006

Hi,

I have a table with 400,000 (approx) records in the following format:

a(PK:AutoNum) - b(text)-c(text)-d(Num)-e(text) - f (text - unique/random)
A B C D E F
1 - 6767 - P2 - 1 - 24/992 - 34341212
2 - 6767 - P2 - 1 - 24/993 - 87657483
3 - 6767 - P2 - 1 - 24/995 - 98764536
4 - 6767 - P2 - 2 - 24/996 - 87543297
5 - 6767 - P2 - 2 - 24/998 - 98674635
6 - 6767 - P2 - 2 - 24/999 - 34546576
7 - 6767 - P2 - 2 - 25/001 - 98768547
8 - 6767 - P2 - 2 - 25/002 - 46576897
9 - 6767 - P2 - 2 - 25/004 - 62536475

I need to create a new field in a query that acts like an autonumber, that resets based on an increase in field (d).

ie:

1 - 6767 - P2 - 1 - 24/992 - 34341212 - 1
2 - 6767 - P2 - 1 - 24/993 - 87657483 - 2
3 - 6767 - P2 - 1 - 24/995 - 98764536 - 3
4 - 6767 - P2 - 2 - 24/996 - 87543297 - 1
5 - 6767 - P2 - 2 - 24/998 - 98674635 - 2
6 - 6767 - P2 - 2 - 24/999 - 34546576 - 3
7 - 6767 - P2 - 2 - 25/001 - 98768547 - 4
8 - 6767 - P2 - 2 - 25/002 - 46576897 - 5
9 - 6767 - P2 - 2 - 25/004 - 62536475 - 6

I initially thought I could make an IIF expression that basically says: if [d] in this row is the same as [d] in the previous row then add 1 to the previous record [new number], otherwise, enter 1 in this record [new number] (thereby resetting it)....

I have searched the forums for 'numbered sequences' (amongst other things), and it appears that either the Dcount or Dmax function appear to be the solution, but I'm having some trouble applying it to this problem. I was hoping someone can help me through it.

Cheers

View 1 Replies View Related

Conditionally Setting The Value Of An Expression

Aug 30, 2006

I am trying to enter an experssion into the grid for age groups. The result will be a text value based on the value in a column named Age. There are three groups, '<18', '18-30', and '31+'. I tried using the following but the result that is returned is #Error.

age_grp: Switch([age]<18,'<18',[age]>=18 And [age]<31,'18 -30',[age]>30,'31+')

Do I have the syntax wrong? or maybe I'm going about it completely wrong.

I have also tried a nested iif statement Age2:= iif([age]<18,'<18', iif([age] >=18 And < 31, '18-30'), iif([age] >30, '31+')). I get the same error message.

This worked for displaying the value on a report =IIf([age]>=18 And [age]<=30,"18-30",'31+'). However, I need to sort and group by the value so I can count the number in each group, so I wanted to put it into a query.

Thanks,
Christy

View 4 Replies View Related

Conditionally Lock Record

Apr 11, 2005

OS/Program :xp/Access 2002

The following problem. Within my main form I have a status field. Dependent of the status of the main field I would like to lock the total record in my sub form at once. Within which event of the sub form could I achieve this and which property locks a total record?

thanks
giovi

View 2 Replies View Related

Conditionally Suppress On A Form

Oct 10, 2005

Hi all

Long time searcher, first time poster!

I have a subform which has loads of check boxes for each record but i want to suppress certain boxes depending on criteria in each record. I think i need to do a For..Each statement but, as my user name suggests, I'm buggered!.

View 2 Replies View Related

Conditionally Format Text Box

Oct 18, 2005

Hi All,

I have built a code database where right now I display my sample code in a textbox on my main form. I was hoping to replace the text box with a Rich Text Box. I could then format the code to sort of match what would be shown in the VB/VBA IDE.

I know the Rich Text Box was disabled in Access 2003 for security reasons. I also know that there is another free RTF control. I was hoping to find out how Microsoft intended for us to replace the Rich Text Box. I could use a Web Browser Control but I was not sure of the best way to add this to my form and then feed it the data that was originally bound to the text box.

Thanks for any insight you would be able to provide.
Steve

View 1 Replies View Related

Change Value Of Unbound TextBox Conditionally

Jan 10, 2006

Can anyone help me out here.
I need to use an unbound text control to return me a value if a field is populated else "Local" if the field is not populated.
Can anyone tell me if this is possible and show me the syntax if it is.
Help appreciated.
Andy

View 8 Replies View Related

Calculate A New Value Conditionally Based On 2 Records

May 1, 2006

I am trying to write an expression that calculates a new field in a query. The new field is simply recoding values from another field (survey question responses) using IIf statements. This new field, let's call it RecodedResponse, will eventually be summed in a totals query to get the total score on that survey for each participant. Each record is a question response and there are multiple records for each participant (16 items on this particular survey). The problem is that for 2 of the questions on this 16 item survey, the responses are supposed to be eventually combined and recoded based on each other. So, my question is:

Is there a way to do a conditional expression where the result from the previous record is used? Or do I need to first do a crosstab query so that all the responses for one participant are on one line?

I am pretty sure I can do a workaround to make it work, probably doing a crosstab, then recoding in another query based on the crosstab, but it would be cleaner (I think) to do this some other way. Any ideas? Or is this too vague?

Thanks,
Liz

View 3 Replies View Related

Queries :: How To Conditionally Update Multiple Columns

Mar 29, 2013

I have a databse of bird ringing (banding) which I have turned into an encouter history by using the pivot table capability in MS Access. Example attached.

For each animal, identified by a ring number in column B, there is a set of "encounters" over time. A blank in a date column indicates that the animal was not seen or captures in that year. A number in the column indicates that it was seen or captured n times.

To format these data correctly for subsequent surival probability analysis, I need to replace all of the blanks, in each of the "year" columns with a 0 and anything that isn't blank (e.g. 1, 2 or 3) with 1. So, I need columns F to L to look like C to D.

It was simple enough to do this one column at a time...

UPDATE [Capture history intermediate] SET [Capture history intermediate].[1971] = "0"

WHERE ((([Capture history intermediate].[1971]) Is Null))

But I have a lot of data, spanning 40 years (i.e. 40 columns) and I will probably have to do this conversion many times as I work on sub-sets of my master database.

So, question is, is there a short-cut way to avoid having to create 40 different queries and run each one seperately?

View 3 Replies View Related

Reports :: Text Label Conditionally Visible On Report?

Dec 31, 2013

I'm running Access 2003 and my question relates to delivery notes that are produced for drivers as reports. These delivery notes have a blank payment box for the driver to complete if the customer pays upon delivery.

As more and more customers are prepaying by credit cards, I'd like this payment box to be automatically filled with a "PAID" text which will let the driver know the order has already been paid for.

The delivery note is produced from an "Orders" table which has a "Paid" field whose value is automatically set to "Yes" when the order has been paid for.

I was hoping to be able to place a "PAID" label on the report which is only visible when the Paid field of the underlying query is Yes.

As things are at the moment I cannot see any way to put any conditional statement onto this Text/Label. The only options for the "Is Visible" property of the text are Yes or No.

View 4 Replies View Related

Reports :: Conditionally Expand Chart Onto Multiple Pages

Dec 13, 2013

Have a generic dashboard report with multiple charts in it. One in particular is a bar chart, but depending on what the dashboard is produced for, the bar chart may have too many bars in it to make it legible. Therefore I'd like to conditionally determine the number of bars (basically the number of records in the query) and then make the chart go from one page and continue onto another.

View 4 Replies View Related

Reports :: Conditionally Show Images On Report Header

Oct 30, 2013

I'm producing PDF reports in a VBA routine in Access through the DoCmd.OutputTo acOutputReport.

The routine works great, however i have now a new requirement and i need to change the report header so to if Country X is selected in a drop-down menu, then only image X is shown on the report header; if country Y is selected then only image Y is shown on report header.

I was simply thinking to make this through the "visible" option of the image, however it doesn't work because i get error "report must be open".

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved