Review July 2003
Review October 2003
Review July 2004
Review October 2004
Review July 2005
Review October 2005...and so on.
I know the data is not normalized, but if I want to use the Year part of the string in a WHERE clause, how would I do that? or in other words how do I extract the Year from the string.
I have a database tracking customers and inventory, and I've tried to add in a feature where I can meet customers needs with future inventory. Key to this process is three tables:
tblCustomer - containing my customer information tblInventory - with inventory data including a part number tblNeeds - a table I've created for this purpose, containing CustomerID and Part Number.
Here's the premise: when speaking with a customer, I may learn they want a product I don't currently have in inventory, or may have never had in inventory, but that I may get in the future. I do this "on demand" in the form of a report, and it works really well ... except:
I have found that several similar products from the same supplier have similar product part numbers. What I would like to do is be able to match my query on a fractional part of the part number. In all cases, the part number would be the same over the first six characters (in a text field). Any ideas how I might do this? In my current query, I use a join, but I can't do that for fractional strings....
I've attached the query I am currently using (and running a bound report from this query):
SELECT DISTINCT tblCustomer.CustID, tblCustomer.CoName, tblInventory.ProductDescription, tblInventory.ProductPartNumber, tblInventory.UnitsAvailable, tblInventory.ProductID FROM tblInventory INNER JOIN (tblCustomer INNER JOIN tblNeeds ON tblCustomer.CustID = tblNeeds.CustomerID) ON tblInventory.ProductPartNumber = tblNeeds.PartNo WHERE (((tblInventory.UnitsAvailable)>0));
And what I want to do is match the tblInventory.ProductPartNumber on the tblNeeds.PartNo by matching only the first six characters....
I am trying to extract the first instance of 3 numbers from a string using an update query to update another field in the same table(Master Equipment) titled "Code". the field is called "Equipment". the strings vary in length as well as the location of the 3 numbers needed, but I have examples of all possible locations the 3 number series could be located below. As you will notice, some fields do not contain 3 numbers together, for these I don't want to return a value.
and I'm trying to pull the max value of the number after the -e- for a given set of them. In this example, I'd want to return the number 70. I'm then going to use that to create the next ID and populate another field.
The IDs are not used as the primary key. And while the previous IDs used leading zeros inconsistently, new IDs will not have leading zeros.
Here's what I have so far, but it doesn't seem to pull the number after the -e- at all. I think this section here is the problem, even though the same logic works in a query:
Code: Public Function MaxArticleERef(hbID As Long) As Variant On Error GoTo err_handler Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSql As String Dim maxERef As Variant
Code: SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address]) WHERE (((tblData2.MyYear)<1990)) ORDER BY tblData2.LineNum;
The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:]) Site - (criteria = Like "*" & [Enter Site:] & "*") Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
I have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1 1 2 1 3
2 1 2 2 2 3 2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
I was looking for some help. I am trying to setup a table with a field for web address. People are entering www.website.com etc however I need them to make sure it starts with http:// Is their any way I can put validation on the field to make sure that this is entered? Or maybe I could use an input mask?
I have a member roster. I have members who have attended. How do I subtracta list of those who have attended from the roster to find those who havent attended. The only way I know those who have attended is by their sign in date.
I have created several Queries and I would like to have a column that numbered the results. I have looked all over saw a couple of samples but havent had any luck. Anyone have any suggestions. Thanks for your help and have a good New Year.
Just to clarify basically this is what I need. Loan Number is the Primary key.
# Column Borrower Loan Number 1 smith 123456 2 jones 023567 3 jang 102569 JW
How could I pull in someone when they do not have data?
There is a name table and data table. My goal is that when I run a parameter query if someone does not have data for that time period to have that name pulled in so that eventually a report could be generated stating there is no data for this person for the time period.
I'm trying to figure out how I can pull the most recent entry in a table for each of the properties in our database. Let me give you some background. This access database is used to format reports from an SQL database - all of the tables are linked and all of my other reports work great except for the one I'm trying to create from a table that gets updated every day by way of a stored procedure that runs every night on the SQL server. In my mind this shouldn't make a difference and I should be able to select whatever data I want from this table/query based upon whatevery criteria I want. The field in the table I'm trying to use is a "date stamp" of when it was updated via the stored procedure. I want the last entry made for each property so I selected "last" as the criteria which should get me approx. 20 lines - one for each property. But when I use this I still get back multiple entries for each property. This seems like it would be easy to do but everything I try doesn't seem to work.
That's why I've turned to the experts here for help.
I'm certainly not new to Access, but this is the first time encountering a problem like this. I have a table with 3 Field Names, [Return], [Header] and [Date]. An example table looks like so..
I need to take to have group the Header's together, for instance 10, and have it return the [Date] for whichever line has the lowest [Return] value. I just can't figure out how to do it. Any help or insight would be GREATLY appreciated. I'm 90% done with the project, and this last part will seal the deal.
I have a field that is updated with =Now() now when I try to run a regular query with the criteria being =Date()
I get nothing And the "=" is always disapearing out of the query. I dont know what is going on. I got the field set up in the table section as a date field with short format.
Can somone please tell me what I am doin wrong when I have followed instructions up to this point....
Hi, I was wondering if someone could check this code over. I am trying to pull a query (titled: Codes_+_Conduct:_General)which was already created in Access. Here's the code: (the red is where the error message I get is)
----------------------- <% Set objCon = Server.CreateObject("ADODB.Connection") objCon.Open connectionstring Set objRS = Server.CreateObject("ADODB.Recordset") strQuery = "SELECT * from Codes_+_Conduct:_General order by subject asc" objRS.Open strQuery, objCon IF rs.EOF Then Response.Write("<tr><td colspan = 3>No Tips</td></tr>") Else Do While not rs.EOF x = x + 1 %>
I trying to build a usage tracker. Users will take a reading once a week and enter data. I want to open the form and have 2 fields filled in based off the last reading entered into the table. I have an ID field (autonumber) and I tried using that field -1 to populate my other fields but it does not work. any suggestions
Ok, so I am creating an Access Database to track the progress of our collections staff. I need to pull data from a remote SQL database and filter it so that the appropriate records are appended to the appropriate tables in teh Access DB. Any ideas?
I would consider myself a novice to almost intermediate with access.
Anyways what I need to do seems pretty simple. I just need to make a web page that pulls values from a table we have stored on our server. The users WILL NOT need to modify the info, they simple need to view it.
I want it set up so that when users modify the table locally here at our company, people from another site can view the changes on the internet...
I've been trying to figure out why my query only pulls record Object ID 10011399 and not the other one, but I can't figure it out. Can anyone please help me see what's wrong?
I'm querying off of a teradata table in Access. I have the teradata table linked to a table I made in Access which will only pull specific cars(example). How can I make the query pull.. like car* for every record I have in the table?For example..My made table has20120220321Q24Retc...the teradata table shows this201G101201G102201P202203A12021QP131the record means ...the first 3 characters is the car name, the middle two char is the location, and the last 2 is the day of the week.Btw in the query I use in my other querying system it has an option "Begins With" which works, I don't know if acces has this? Can anyone assist me on this? If I am unclear let me know!!
I've linked access to an external database and created a query to pull results from the linked access table that was between certain times on certain dates (i.e. between 11/12/2007 11:01 AM to 12/13/2007 12:01 PM). The query I have thus far is "SELECT * FROM TABLE1 WHERE DATETIME >= #11/12/2007 11:01 AM# AND DATETIME <=#12/13/2007 12:01 PM#". That works, but I want the date and time to be changeable through text fields on a form. Something like "SELECT * FROM TABLE1 WHERE DATETIME >= #forms.form1.startdate forms.form1.starttime# AND DATETIME <= #forms.form1.stopdate forms.form1.stoptime#". The dates are going to be automatically generated according to other parameters but the times are going to be entered manually. How can I accomplish this?