code wild card value for form variable

i use a check box in an option group to generate a specific value for a 
variable.
 using the following code

   If Forms![fee sched form]![select region] = 2 Then
   Forms![fee sched form]![region var] = "Roch"
End If
If Forms![fee sched form]![select region] = 3 Then
   Forms![fee sched form]![region var] = "CNY"
End If

this variable is hidden on the form and value is used a query.

how do i code this, so i can put "*" in the variable and thereby get all 
values in the query?

the query code generates like "*" but i do not know how to code via the 
forms command

thanks for your help

0
Utf
10/15/2007 7:19:00 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
860 Views

Similar Articles

[PageSpeed] 19

You can set the text box to Null, and then in the query add the criteria

Select * From TableName Where FieldName = Forms![fee sched form]![region 
var] Or Forms![fee sched form]![region var] Is Null

So if the value is null the query will return all records

=============
Also, instead of using a hidden object in the form with code you can set the 
criteria in the query

Select * From TableName Where FieldName Like IIf(Forms![fee sched 
form]![select region]=2,"Roch",IIf(Forms![fee sched form]![select 
region]=3,"CNY","*"))

-- 
Good Luck
BS"D


"jnewl" wrote:

> i use a check box in an option group to generate a specific value for a 
> variable.
>  using the following code
> 
>    If Forms![fee sched form]![select region] = 2 Then
>    Forms![fee sched form]![region var] = "Roch"
> End If
> If Forms![fee sched form]![select region] = 3 Then
>    Forms![fee sched form]![region var] = "CNY"
> End If
> 
> this variable is hidden on the form and value is used a query.
> 
> how do i code this, so i can put "*" in the variable and thereby get all 
> values in the query?
> 
> the query code generates like "*" but i do not know how to code via the 
> forms command
> 
> thanks for your help
> 
0
Utf
10/15/2007 7:46:00 PM
jnewl wrote:

>i use a check box in an option group to generate a specific value for a 
>variable.
> using the following code
>
>   If Forms![fee sched form]![select region] = 2 Then
>   Forms![fee sched form]![region var] = "Roch"
>End If
>If Forms![fee sched form]![select region] = 3 Then
>   Forms![fee sched form]![region var] = "CNY"
>End If
>
>this variable is hidden on the form and value is used a query.
>
>how do i code this, so i can put "*" in the variable and thereby get all 
>values in the query?
>
>the query code generates like "*" but i do not know how to code via the 
>forms command


You didn'r say what condition(s?) indicates that * should be
used.  Whatever it is, just use the same kind of thing that
you already have:

If somecondition Then
	Forms![fee sched form]![region var] = "*"
End If

Set the field's criteria to:
	Like Forms![fee sched form]![region var]

-- 
Marsh
MVP [MS Access]
0
Marshall
10/15/2007 7:57:31 PM
tried that before sending.  what happens the quote marks get dropped, when 
the value is stored in the variable. so instead of "*", you get *. the query 
does not understand like *, so as a result get nothing.

so tried using a variable called quote and set that equal to "

then did region var = "like " & quote & "*" & quote.

got the same result of like *



"Marshall Barton" wrote:

> jnewl wrote:
> 
> >i use a check box in an option group to generate a specific value for a 
> >variable.
> > using the following code
> >
> >   If Forms![fee sched form]![select region] = 2 Then
> >   Forms![fee sched form]![region var] = "Roch"
> >End If
> >If Forms![fee sched form]![select region] = 3 Then
> >   Forms![fee sched form]![region var] = "CNY"
> >End If
> >
> >this variable is hidden on the form and value is used a query.
> >
> >how do i code this, so i can put "*" in the variable and thereby get all 
> >values in the query?
> >
> >the query code generates like "*" but i do not know how to code via the 
> >forms command
> 
> 
> You didn'r say what condition(s?) indicates that * should be
> used.  Whatever it is, just use the same kind of thing that
> you already have:
> 
> If somecondition Then
> 	Forms![fee sched form]![region var] = "*"
> End If
> 
> Set the field's criteria to:
> 	Like Forms![fee sched form]![region var]
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
10/16/2007 12:19:02 AM
My mistake.  Let's try setting the query criteria to

Like """" & Forms![fee sched form]![region var] & """"
-- 
Marsh
MVP [MS Access]


jnewl wrote:
>tried that before sending.  what happens the quote marks get dropped, when 
>the value is stored in the variable. so instead of "*", you get *. the query 
>does not understand like *, so as a result get nothing.
>
>
>"Marshall Barton" wrote:
>
>> jnewl wrote:
>> 
>> >i use a check box in an option group to generate a specific value for a 
>> >variable.
>> > using the following code
>> >
>> >   If Forms![fee sched form]![select region] = 2 Then
>> >   Forms![fee sched form]![region var] = "Roch"
>> >End If
>> >If Forms![fee sched form]![select region] = 3 Then
>> >   Forms![fee sched form]![region var] = "CNY"
>> >End If
>> >
>> >this variable is hidden on the form and value is used a query.
>> >
>> >how do i code this, so i can put "*" in the variable and thereby get all 
>> >values in the query?
>> >
>> >the query code generates like "*" but i do not know how to code via the 
>> >forms command
>> 
>> 
>> You didn'r say what condition(s?) indicates that * should be
>> used.  Whatever it is, just use the same kind of thing that
>> you already have:
>> 
>> If somecondition Then
>> 	Forms![fee sched form]![region var] = "*"
>> End If
>> 
>> Set the field's criteria to:
>> 	Like Forms![fee sched form]![region var]
0
Marshall
10/16/2007 4:15:03 AM
Reply:

Similar Artilces:

Temporary Variables
I have a pointer stored in TempVars!MyIndex Within DLookup(.....) or DSum(.....) references to TempVars!MyIndex work fine but IIf(tblField = TempVars!MyIndex,.....) does not work. As a work-around I tried this: Dim NewIndex as Integer NewIndex = TempVars!MyIndex IIf(tblField = NewIndex,.....) and found it works well. So just what is the difference between temporary variables and numeric table fields? ---- Ken hi Ken, On 10.12.2009 04:23, KenB wrote: > I have a pointer stored in TempVars!MyIndex > Within DLookup(.....) or DSum(.....

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

Not existing value
Hi, I want ask you that ... I have a list box in a form and my question is that, how to cancel the new value of the list box when the user gives a value which does’t exists in the list. Thankig you in advance for your collaboration Simon wrote: >I have a list box in a form and my question is that, how to cancel the new >value of the list box when the user gives a value which does�t exists in the >list. In a list box, users can not enter a value. Sounds like you mean a combo box, in whiich case you can just set the combo box's LimitToList property to Ye...

How to requery from a form
I have a form that has a text box on which i would like to type some text into then i would like it to do a requery every time i change the text. but it not a requery on the query the form is based on it needs to requery on a different query thnak "Simon" <S.Dickson@shos.co.uk> wrote in message news:1176363215.854267.69050@d57g2000hsg.googlegroups.com... >I have a form that has a text box on which i would like to type some > text into then i would like it to do a requery every time i change the > text. but it not a requery on the > query the form is based on ...

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

How to create a drop down list with different values
I'm a beginner of Excel 2000 and I would like to create a drop down list on the airfares, where different locations can be chosen, and when a location is chosen, the cost will appear in the colums on the right. Expenses Unit Cost 1 Day 2Days 1. Airfares (here is where I need help) 2. Hotel $100 $100 $200 3.Transport $100 $100 $110 Total xxx xxx xxx eg. If HK-GZ is chosen, I can see the cost appears in the same row. The end result will be getting the total sum of all the ...

Business Card Numbering
Is there any way I can get publisher to auto number business cards on a single sheet from say 1 - 10, then 11 - 20, and so on? There is full information here: http://www.users.bigpond.com/auslandline/publisher/ If you need any further assistance don't hesitate to post back here. -- Yes, but not if you start with the Business Card wizard. Here's a technique which will allow anyone to number any size document. First, create a list of numbers in a format that Publisher can merge from. Publisher can handle most database formats, including text lists, but the easiest way is probabl...

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

Credit Card Account on Money 2002
I pay my credit card account using the Money register. I now am trying to run a Monthly Income and Expenses report and I find all my payments coming up in "Income Unassigned." When I click on one of those individual payments, it shows "From XYZ Credit Card" being paid to "XYZ Credit Card." Obviously I have set the accounts up incorrectly, but I"m not sure what to do. Can anyone help? Something tells me you are messing with electronic transactions and downloaded transaction data without really understanding how Money works first. Perhaps I'm wrong....

How to reference variable rectangular range?
I have the following variables: Const cellName As String = "B2" Const nRow As Long = 5 Const nCol As Long = 10 I want to reference the rectangular range starting from cellName through row cellName+nRow and column cellName+nCol; that is, B2:K7 for the values above. I've come up with the following: Range(cellName, Range(cellName).Cells(nRow, nCol)) Seems tedious. Is that the best I can do? You would use the Resize property... Range(cellName).Resize(nRow, nCol) Note, however, that you counted your rows and columns differently... I get Column K for nCo...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

Conditional enable on a continuous form
from what I am reading in the current posts, this may not be an option. It's the old dreaded applying conditons to subforms which are continuous forms. Objective: if X value is in TextBox_A then TextBox_B is enabled. If Y value is in TextBox_A then TextBox_B is disabled. Applying this to OnCurrent event of the subform makes all TextBox_B's behave the same way. And applying this to the AfterUpdate event of TextBox_A also applies this to all occurences of TextBox_B. I am working in A97. recommendations? Not possible in A97. With A2K or later you can use Conditional Formatting t...

Min, Max Value Labels in Line Chart
How do I label the minimum and maximum values of a series in a line chart? Thanks in advance from this Excel beginner. Here's one way: http://peltiertech.com/Excel/Charts/FormatMinMax.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Freddy" <none@none.com> wrote in message news:YbWdnUsnYJYJnPzZRVn-vQ@giganews.com... > How do I label the minimum and maximum values of a series in a line chart? > > Thanks in advance from this Excel beginner. > ...

Averages of absolute values
Hi I'm switching over from QuattroPro to EXCEL and am having some newbie problems. I have a list positive and negative numbers (A1..A25). I would like to calculate the average of the absolute values. The only method I have been able to come up with is to calculate the absolute values in column B using B1=ABS(A1), B2=ABS(A2), etc. Then use A26=AVERAGE(B1..B25) and hide column B. Is there a better way to do this? In Quattro Pro I would use @AVG(@ABS(A1..A25)), but a similar method doesn't seem to work in Excel. Thank you, Rob Ron Array enter the following formula: =SUM...

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

Open a form using a comand button on another form
By using a command button on a certain form I am oppenig an other form at a cetain record by using some criteria. I am using the command button wizard to do that. When the form oppens however, I want to be able to move to other records of the form record source. Is there an easy way to do that? Thanks in advance Michalis Instead of passing the criteria in the WhereCondition, pass it in OpenArgs instead. Then use the form's Load event to FindFirst in the RecordsetClone, and set the Bookmark. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http:/...

Typing an adress in a form and verify if it exist in another form
Hi, One form for user without permit. One form for user with permit. If a user without permit takes a permit, a click on a button transfer the user in the form for user with permit and destroy the user without permit. But ! Sometimes a user with permit is created, and we realize to late that the same user already existed in the form for user without permit. I would like, after finishing to type the user address, using the form for user with permit, to verify if the address already exist in the table for user without permit. In both form, the address is compose of three fields : number, apa...

how to divide all values with a fixed value ?
how can i divide all values in one data column in a pivottable with the same data value in another column in the same pivot table ? how do you specify the reference in the calculated field so it always takes the same 1 value ? this is very urgent. thanks in advance. -- fax ------------------------------------------------------------------------ fax's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28912 View this thread: http://www.excelforum.com/showthread.php?threadid=486922 I think what you're looking for is an Absolute Reference. So, let's say yo...

Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...

Need to check if the value in one cell is correct or not
Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digi...

Customize Lead Phone Call Activity form and Add New Activities
Help Please??? I have a new MS-CRM implementation and I have some questions on customizing the activity forms and the types of activities available: 1. How do I customize the Lead Phone Call Activity form to add fields like Company name and City, etc.? Shouldn't this be able to be done with the built-in form editor? Since it seems that phone call activities have an object code of "137" (got this by digging through the Form Editor’s default XML files), I tried manually going directly to: http://crm/tools/formEditor/formEditor.aspx?objectTypeCode=137 but the form that app...

Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...