Using a cell to determine a value in a database query.

Hi folks,

I've run into a problem recently - maybe there's no good answer, then
again perhaps you can help me come to one.

I have a spreadsheet on which There are three entry fields:

start date
end date
store number

I've managed to map start date to [startdate] in a query and end date
to [enddate] - that seems to work fine.  The problem I'm running into
now has to do with the store number field.

In the database, store number is a 4 character field, as of now the
numbers of the store are like 01,02,03,04..HQ,RO.  When I try to map
store number to [storenum] in the query, I get no rows - nothing comes
back to excel.

I did a little playing with the query and I've figured out that when I
hard code the store number criteria as like '%03%' I can get store 3's
information.  If I leave it as a [storenum] and enter 03 into the
spreadsheet again I get no rows back.  Even when I use equals instead
of like, there's no improvement.

Is there a way to make the spreadsheet pass %03% to the query, or to
make the query make a %[storenum]%.  I have a feeling there's more
viability to the first option, as %[storenum]% isn't the right

If you have any ideas or input please respond.


Ben / ND

nickdangr1 (14)
9/21/2005 4:16:45 PM
excel 39879 articles. 2 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 24


Similar Artilces:

Auto Fill Cells
I am building an inpatient log for a nursing home. I need to auto fill the patient's name and address from their ID # Example in column C (ID#) 255 - would automatically fill in Column D (last name), Column E(first name), Column F(middle initial), and Column G(address) At this point - all of the cells are blank. Sounds nice. Where is the data coming from? Take a look at these links for some ideas of how you can go about doing this:

comments in a cell #2
HYCH, Just a question? is there any way of using some vba that will add coments to a cell and have the comment box appear as it does if i add my own comments manually, would like the comment hidden until the cell is selected and then have it show up! obviously i know this happens when you manually process comments, but i would like the comment box to appear and display values that are in another worksheet within the same workbook. hope that makes sense. Steve Steve wrote: > > HYCH, > > Just a question? > > is there any way of using some vba that will add coments t...

Formula for counting specific cells which are greater than 1
I'm looking for a formula that will count specific cells (not a range - A17, A27, A37 and so on) that will have a certain value (above $1.00 to make it easy). So if the cell has a value of $1.00 or less it will not count in the total; If it is above $1.00 if will count. If A17 is above $1.00, A27 is $0.50 & A37 is $3.00 the count will be 2. ...

shading cells that are not consecutive
Is it possbile to shade cells B10-16 and D14-8 and F2 and etc and forma them as a group? Thank yo -- Message posted from Use Ctrl to select all the rages you want selected then do th formatting you wish. Either that or choose one cell and do the formatting the way you want Then copy it and select the other cells and go to edit: paste special formats -- Message posted from ...

Replacing cell with text
Hi, I want to know if it is possible to do the following: Imagine I have a legend in which I say which text corresponds to whic letters ex: John -> J Harry -> H Susan -> S and what I want to do is when entering J, H or S in a cell, it wil change to the corresponding text. Is there a way of doing it without having to run a macro ? Thanks in advance Pedr ----------------------------------------------- ~~ Message posted from ~~View and post usenet messages directly from Go to Tools > AutoCorrect Options (XL2002) and insert new...

Query Criteria 05-24-07
Finding it hard to get my head round this so help needed! I have a query based on form fields. There is date range field (always selected) and 2 other optional criteria needing 4 WHERE/OR statements to get the required results. I want to add another optional criteria field which i think will leave me with 11 WHERE/OR statements and a big headache! Is there a simple way around this? ...

Converting Text Values to Dates
Is there a way to convert a column of text, date values (entered as 81096, for example) to valid date format, 8/10/96? I could not get the DATEVALUE function to do this. Hi! Select the range of cells in question. Goto Data>Text to Columns Click NEXT twice Select Column Data Format: DATE. Then choose the format from the drop down Finish Biff "Frank Winston" <> wrote in message > Is there a way to convert a column of text, date values (entered as 81096, > for example...

Dynamicly change spreadsheet tab names depending on cell value
Is it possible to change tab names dynamicaly ? For example i have 31 tab (1 for each day of the month) These tabs are named - 1,2,3 e.t.c Is it possible to define rule that would ad "!" to day which is weekend day. In other words is it possible to dynamicly change tab name depending from value of cell ? If this is possbile how do i do this ? Right-click on the spreadsheet tab, select View Code and paste this in:- Private Sub Worksheet_Activate() ActiveSheet.Name = Range("A1").Value End Sub To test this I put the date in cell B2 and this formula into A1:- =TEX...

Could Excel be used as accounting software?
If so, any tutorials. (I don't want to learn or bky quick books) I jus want to keep tabs on business expenses and I would rather not do it o paper -- Freddy_Kruge ----------------------------------------------------------------------- Freddy_Kruger's Profile: View this thread: Theoretically yes but practically no. There a lot of other softwares particularly databases that could do a lot than Excel. These include SAP,Oracle etc., Are you saying that excel wou...

Add item using HTML Status Bar
I want to add a bunch of pictures of items to the HTML status bar and have the item added to the transaction when the cashier clicks on the item. Is that possible? I have the customization guide, I can modify the status.htm file to display the pictures, but I just need the code to add to the HTML file to trigger the adding of the item (again, if possible). P.S. I don't want to use the custom POS buttons (I already know how to add items that way). I almost have it figured out. I added this code to the Status.htm file: function addit() { qsBridge.FireEvent("PerformAddItem...

Opening a database with a new form
Hi I am creating a database in which when users open it, it defaults to a single form. At the moment, when they open it, it opens on a record. Is it possible to open it so that it's a new form (i.e. record) instead of a previously created record> Thank you in advance for your help. You can set the data entry property of that form to No. It will open ready to add a new record. If you want users to be able to go back to other records, you can use code in the load event to make the form go to a new record (without setting the data entry property to No). DoCmd...

substract cell F from cell H and total into cell I
i am new to using excel. i need to be able to put in pay price in cell F and selling price into cell H and get total in cell i. so it would have to substract cell F from cell H and put total into cell i. how do i do this? currency only Assuming your data is in Row 1, put this formula in I1 and press sure and type it just as shown, as the leading equal sign is what tells Excel that the following characters are a formula. =H1-F1 Vaya con Dios, Chuck, CABGx3 "vadarpug" <> wrote in message news:617768AC-D261-47AE-861A-E6BFD...

How can I get current cell row number
I need to reference the row value of the current cell in a worksheet function: = row() in VBA: activecell.row Hope this helps Rowan excelneophyte wrote: > I need to reference the row value of the current cell ...

Excel front ends to Access Database
We are in the process of converting all our old 95 databases and I have one with excel front ends. Upon converting the DB to Access 2000 and reentering the Excel front end the DB is unrecognized. Any suggestions? Thanks! ...

how to create button commande to refresh data in query in excel 2.
I have a excel spreedsheet that contain external data. I would like to put a button in the excel sheet to update the sheet without doing right click and refresh. My user here are very dummies. Jean Francois If this is external data then bringing it in should launch the external data toolbar. They will only need to click the exclamation (!) mark. Seems like re-inventing the wheel. If you must then put a button on the worksheet and assign it to this macro Sub refreshdata() Dim wks As Worksheet Dim qryTab As QueryTable Set wks = ActiveSheet For Each qryTab In wks.QueryTables qryTab.Refr...

Change a cell's value using a button
Hi - I know its possible but have no idea how to do it - How do I set up a cell with two buttons attached to it that increase and decrease the value in the cell? I.e. if I have a cell with 10% in it and hit the "up" button, the number goes up by 0.5% and down by the same amount if I hit the "down" button? I am pretty good in Excel but dont know anything about macros - but Im really not sure where you find this function. Thanks for your help in advance anyone! -- Ashley No macros required. Use a spin button control rather than 2 buttons. Follow these steps: 1. Go to...

I need complete idiot proof excel setup database help. please
I need to learn all about Excel and how to make a database.. I have read and read and done practise tutorials but I just cannot grasp the concept, it completely goes over my head everytime. I am quick to learn when I am being shown or told exactly what to do, can anyone help me learn all about Excel.. ? The first thing you need to know is that Excel is not a database application, and while it is commonly used for databases, it doesn't do them well. Expecting a database developed in XL to be "complete idiot proof" is setting yourself up for disaster. Without knowing any...

Filtered Rows using Mode Function?
Hi Excel Forum, I am using numeric "filtered" data and I need to find the most frequen / re-occurring values (1st, 2nd, 3rd, 4th, 5th etc.) from the visibl filtered rows. The Mode function seemed likely, but I cannot get it t work with filtered rows. Can you assist with working examples, please: Formula based input direct on worksheet. VBA Macro using Formula. VBA UserDefined Function. Thank you QT -- Message posted from I would use one of 2 methods :- 1. Formula in another column and sort descending :- =COUNTIF($A$1:$A$20,A16) Need to sort out dupl...

problem with fonts using Word as editor #3
All new messages come up with a superscript font. I remove the check mark and go into default, but new msgs keep coming up in superscrip.What is one to do? Leo Leo Kerner <> wrote ... > All new messages come up with a superscript font. I remove the check > mark and go into default, but new msgs keep coming up in superscrip.What > is one to do? Since you keep asking, I'll reply with my first reaction to all word-as-email-editor problems: don't use Word as your email editor. It's a simple and 100% effective solution to this and a whole host of ot...

Calculate Formulas in Highlighted Cells Only--not whole sheet
In office 97 I was able to recalc only the cells highlighted (control + L) as opposed to the entire sheet or workbook. In Excel 2002, how do I recalc only the cells I have highlighted? Thanks I don't recall this shortcut in xl (any version). I'm guessing that you had a macro that did something special. In xl2002, you could use a macro like this (assigned to ctrl-shift-l) Option Explicit Sub testme() Selection.Calculate End Sub But I think I've read posts that calculating a single range can be bad--it can screw up the calculation dependencies. I think that Charles Will...

Trouble with contacts backup using Windows Mobile Device Center.
(Also posted in old thread relating to the subject) Hi, I joined the forum specifically for this problem and this is my first post. I'm just going to spare you all the introductory bs and get down to my concerns. And I didn't bother reading past the first page of this thread even though it is apparently like four years old and a solution has probably already been found. Forgive me, but I'm really frustrated because I'm pretty good with computers and I've been at a standstill trying to fix this for about 5 hours now. I'm running Windows Vista Home Editi...

Passing Query Parameters from BP to a URL
Hey Eighties Fans: After constructing a query in Business Portal, is it possible to pass a parameter from that query into a URL? Doing so would be similar to the SmartList Builder feature where you can create a web site (URL) "Go-To" link and pass a parameter within that link that literally becomes part of the web site address that appears in your web browser. Thanks! childofthe1980s ...

formatted values from single equation
I would really like to have a Excel spreadsheet with two worksheets, the first sheet has raw data (16 across, 100's down) and what I would like to do on the second sheet is add to the first row, 16 equations (lets say for now y=x). then underneath this the formatted data where x is cell A1 on the first worksheet and y is on the second worksheet. Obviously you would just normally in each cell on worksheet 2 type the equation in (='worksheet1'!A1), but I would like to show people the equation being used. Is this possible, does this message make any sense?? Nice -- mr_nice! ---...

User form Default value in text box
Hi all. Quick question. I have a user form in excel which has a text box which i want to have the value defaulted to the current date. I've looked at several posting on this site but haven't gotten it to work yet. Any ideas on how to set the text box with the current date as a default value? Any help or direction is appreciated. Thanks. -- Message posted via Hi, Try this way Private Sub UserForm_Activate() TextBox1.Text = Date End Sub -- Mike When competing hypotheses are oth...

Dynamic Range Defined by Value of Cell
I am just getting started with dynamic ranges. I have data as follows: A B C Product1 qty cost Product2 qty cost .... ProductN qty cost TOTAL qtytot costtot The number of products varies. There is other data below this that is unrelated. How can I define a range dynamically that will always capture A:C and as many rows up and including the first time it finds "TOTAL" in column A? Any help greatly appreciated. Thank you. I don't know what you mean by "define a range" b...