I am creating a database for a hyperthetical car hire company. I have created three tables, customer table, car table and bookng table. The customer table is linked to the booking table using a Customer ID (autonumber). The car table is linked to the booking table using NumberPlate. The primary key for the booking table is Booking ID.
The cars are catagorised in different groups e.g. hatchback, sports car etc. The customer will choose a car type which is entered into the booking table. They will also enter the dates for which they want to hire their car.
I want my database to take these dates and the type of car, search through the car table and return a list of cars of that type which are availiable on these dates.
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField] "kick the ball" "return the pass"
my query result should looks like this
[Orig] [new] "kick the ball" "kick" "kick the ball" "the" "kick the ball" "ball" "return the pass" "return" "return the pass" "the" "return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
Maybe I've been looking at the monitor for too long but... I need to have a query return data based on a date field or return all dates greater than 1/1/2001 if left blank.
criteria: [Enter date - mm/dd] Or >#1/1/2001#
When I leave it blank it gives me all dates as it's supposed to but when I type a date it does not filter by that date, I still get everything.
I also tried [Enter date - mm/dd] Or Like "*" but it behaves the same way. I know it has got to be easy but my skull may be too thick to let me see it.
In a query, I am using the following code to return the average length of each tenancy. This works fine unless the length is 0. If so, it only brings back 'blank'. How can I change this code to return a 0 value?
Can anyone tell me how to send a Carrage Return in Access? When I try to use the 'SendKeys with{Enter}', Access starts to run in an endless loop that seems to be some Wizard. All I want to do is go to a new line within the same text field.
Can any one help me? I'm an amature at Access and VBA.
When running a query, I want to hide duplicates records for a given field. PO# will have many duplicates, depending on how many part numbers were order on the same PO#. Other unique fields are not displayed. Can an expression help with this? If not, how do I go about hiding them in a query?
When the SSN field is null I would like this query to return : 000000000, is this possible and if so could you show me how? Thanks
SELECT [CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID AS [IVA #], [CASEHEADS RECEIVING NON WELFARE MONEY].MEM_SSN AS SSN, Sum([CASEHEADS RECEIVING NON WELFARE MONEY].AMT_DISBURSE) AS AMT, [CASEHEADS RECEIVING NON WELFARE MONEY].DT_DISBURSE AS [DATE] INTO [CASEHEADS RECEIVING NON WELFARE MONEY TBL2]
FROM [CASEHEADS RECEIVING NON WELFARE MONEY]
GROUP BY [CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID, [CASEHEADS RECEIVING NON WELFARE MONEY].MEM_SSN, [CASEHEADS RECEIVING NON WELFARE MONEY].DT_DISBURSE ORDER BY [CASEHEADS RECEIVING NON WELFARE MONEY].IVA_MEMBER_ID;
I hope this is a simple query becuase my brain has drawn a blank on it.
I have a query with a parameter which a form textbox. If the text box has X value I want to use one list of parameters and if has Y I want to list all rows in the query.
Hi. I have 2 queries, one that returns all the project numbers in the db (say 50 projects), and one that returns some of the project numbers (say 30 projects)
I can make a query that combines the 2 queries, and it returns project numbers that are in both queries (30 projects), but I would like the query to return the 20 projects that are in first query, but NOT in the second query.......does this make sense?
I am sure this is fairly easy.......it has just got me stumped!!
I have three queries that count non-compliant records in each of three catagories per month. One catagory has records for both Jan and Feb and returns the rersults as follows: Jan 3 Feb 4 The other two vatagories however have no non-compliant records for January so they return as follows: Feb 2 Feb 1 I wanted to tie the three queries together in the form for reporting:
Cat 1 Cat 2 Cat 3 Jan 3 0 0 Feb 4 2 1
Is there any way for the queries to return a zero for any month with no records rather than not returning the month at all? Thanks for any help.
This is probably the first of many questions. I have an Access2000 DB with two tables. Each has a field called ParID. I want to have a form with Table1 and Table2 listed. I want to query on a parcel number I keyin. If Table1 has the number in it, I want the form to note such. The same goes for Table2.
Example: Keyin: 123
If Table1 has 123 in it and Table2 does not, my form would note:
Table1= YES Table2= NO
If they both had 123 in it: Table1= YES Table2= YES
Is there a good way to return an id that is created when I insert values into a database? So I have this: INSERT INTO assignedReview ( reviewUser, reviewedEmpuser) VALUES (@reviewUser, @reviewedEmpuser); And from that I want to get the reviewID that is generated (setup as a random autonumber) on the INSERT to pass back the user.
I am looking up a product based on a unique code in tblProducts...I need to return one of two values
If the value of [Product type] = "multi" then return "multi" else If the value of [Product type] = "solo" then return the value of the non empty field in a range of fields
for example
tblProducts PID (key) | PACKAGE TYPE | RED | YELLOW | BLUE C13T0714010 | single | <empty>| yellow | <empty> C13T0754010 | multi | red | yellow | blue
Here's the scenario. I have 3 fields [AgentID], [QA Date], [Points Issued]. What I want to do is run a query that will give me the last date [QA Date] a point [Points Issued] was issued for each agent [AgentID]. And I want the query to display all 3 fields according. Is this possible?
I have a database called LettersDatabase this databse holds all the letters that have been made including the path to the doc. I use SSN to ID the letters to customers on the Contacts Database.
Contacts database also uses the SSN to id the contacts
I have a form that creates new letters for customers in this form I have listbox that queries the LettersDatabase for all matching records based on the forms contact SSN to see how many letters have been made for that customer.
The problem is that my listbox only shows the first record matching that SSN but there are more records in that LettersDatabase with the same SSN that I need to have diplayed on the listbox as well.
I may be writing the query incorrectly. Here what I have for the query on the listbox
Like[Forms]![LetterMaker]![txtSSN]
I try adding (&"*") to the end of the query but that does not help.
If anyone out there has the solution to this problem it would be greatly appreciated
HI, i have a field in a query called [cost_type], these typically contains the values "principle works" or "additional works" how can i set the query up so that it doesn't return the "works" part of the record? Could i just return the first 10 characters of the result? thanks
Ok, I have a query that returns a case manager, their clients, and a date attributed to each client. Ex:
Case Manager Client Order Date James Bond Pussy Galore 01/05/2005 Oliver Cromwell Janet Roundhead 05/25/2005 Alan Turing E. Nigma 08/20/2005 James Bond Holly Goodhead 12/18/2004
What I would like is for the first James Bond record to disappear, as it is later than the last record, giving me only the FIRST Order Date for each Case Manager. Is this possible? If so, what is the criterion expression?
