Super Snazzy Search Functions Needed
Feb 5, 2008
This Forum is a God Send and everyone is so helpful. Ghudson's Audit Trail code saved me many hours of hair pulling :) Anyone out there who may be able to help with my Audit Trail Query?
Anyway, I've been developing a database for the last 2 weeks (night & day it seems!) to track "bugs" found in the code we (not me) develop, suggestions & new requirements and just when I thought I'd got it cracked they came up with the next requirement they'd forgotten to mention!! :eek:
I need a super snazzy search function. The top half of the screen is a form with various combo boxes of which some or all of the values can be selected i.e reference no. author, between dates and a keywords field (a bit like the search page at the beginning of this forum!). On clicking the 'Select' button all the records that meet the input criteria appear in the bottom of the screen in a data sheet view (Ref No. Description & Date). The user can the Double Click on the record they wish to view and they will be taken to the full record in the DataEntry Form.
I'm sure this isn't as complicated as it seems to me at the moment but I am so stumped I don't even know when to start. I've been writing databases for years using the wizards within Access but only recently started to experiment with code. I would really appreciate any assistance forum members can give me but as I am new to these complications please provide 'idiot proof' answers so I can understand what I am doing. :)
Thanks in anticipation
Helen
View Replies
ADVERTISEMENT
Jan 4, 2007
Hello, guys. First of all, may all of you have a very cool New Year 2007!
In my MS Access database i have 2 tables: Table1 and Table2. These are related by means of the fields Field1 (Primary key of Table1) and Field2 (Secondary key of Table2). The relation is 1 to Many.
I pretend to count the rows from the Table2 with different Field2 values. Thus, the following query would be an ideal solution:
SELECT COUNT(DISTINCT Field2)
FROM Table2
Unfortunately, the DISTINCT clause is not admitted within COUNT parenthesis. So, what i came up with is:
SELECT COUNT(*)
FROM Table1
WHERE Field1 IN (SELECT DISTINCT Field2 FROM Table2)
It works, but it takes too long to run the main query, where i embedded several subqueries like the one above.
Is there any other way to get the same result reducing the response time?
Thanks for your attention in advance.
View 5 Replies
View Related
Jan 12, 2006
Hi everyone,
Thanks for taking the time to read this.
I will firstly give you the low down on the business that I am doing the database for. It is a Car Restoration Business, where we deal with vintage/classic/muscle cars and restore them to the former glory.
I do not know Microsoft Access very well, I have attached a file with what I think is a databae :) I could be wrong. The main function of this database is to search for suppliers for a specific car part for a specific car.
So if I want to find a doorhandle for a 1970 Ford Mustang, I need the suppliers pertaining to those criterias to come up.
How do I get this to happen?
Thankyou for your help
Jess:confused:
View 2 Replies
View Related
May 30, 2006
Hello,
I have got a serious problem with parameter searches. What I want are multiple field parameter searches in one query. I created a new query, and set the criteria to Like "*" & [...] & "*" for at least 5 fields. The problem now is, that the number of records get less the more parameter searches i create for different fields.
I tried to put the parameters into the "or" criteria but it still does not work!
please give me a hint.
Thank you!
John
PS please ask me when you did not understand my question.
View 1 Replies
View Related
May 30, 2006
Hello,
I have got a serious problem with parameter searches. What I want are multiple field parameter searches in one query. I created a new query, and set the criteria to Like "*" & [...] & "*" for at least 5 fields. The problem now is, that the number of records get less the more parameter searches i create for different fields.
I tried to put the parameters into the "or" criteria but it still does not work!
please give me a hint.
Thank you!
John
PS please ask me when you did not understand my question.
View 1 Replies
View Related
Jun 6, 2005
I have one PC running win98,acc97 and Access is CRAWLING. I'm seeing this on ONLY this one PC, and in multiple databases.
Any ideas? Possible places to look to fix this? I've tried reinstalling Office, I'd rather not have to rebuild this pc.
Thanks,
-Mike
View 3 Replies
View Related
Jul 25, 2005
Well Super Complicated to me. I have setup two queries as follows,
Query 1
The function of the query is basically as follows.
1. If Field 1 is blank do nothing.
2. If Field 2 is blank then Add 7 days to Field 2.
3. If this falls over a weekend then Add another two days.
The expression I have added to do this is,
IIf(IsNull([Field1]),"", IIf(IsNull([Field2]),DateAdd("d",DateDiff("ww",[Field1],DateAdd("d",7,[Field1]),7)*2,DateAdd("d",7,[Field1]))))
Query 2
This query check whether the result of query 1 is a Weekend and if so adds another two days to it,
IIf(Weekday([Field2]= 1 Or Weekday([Field2])=7,DateAdd("d",2,[Field2]),[Field2])
My biggest problem is if I try and do any filters on this information then I get "Expression to complex" errors.
Am I over complicating things here?
Any ideas would be greatly appreciated.
JC3
View 1 Replies
View Related
Mar 4, 2006
I am brand new to Access/VB as of Thursday. I am an aspiring programmer still in college. My proficiencies are more to the c++/HTML side of things right now, but I am in dire need of some assistance. I was recently given a series of projects to determine if I am qualified for a particular job. I have completed them all but one. I am modifying an invoice form in Access. I have accomplished all but two of the tasks on this last project. I need to modify the invoice form so that whenever a payment amount is entered, the payment date box I created populates with that date. I know I'm really close to it, but I keep getting errors and the only thing populating the box is #Name?. I also need to open up the print/preview mode of the invoice report when I click the print button as opposed to the current form it opens up to print from right now. If anyone could offer any assistance/advice on anything...even a tutorial that might point me in the right direction that would be excellent. I have until Tuesday to figure this out =) Thanks a bunch in advance!
P.S. This is where I thought I needed to execute the update for the payment date since it is where the payment is entered. If this doesn't help at all then I apologize.
Private Sub I_Payment_Exit(Cancel As Integer)
Me.I_Tax = NullToZero(Me.I_Subtotal * Me.I_TaxPerc)
Me.I_Total = Me.I_Tax + NullToZero(Me.I_Subtotal) + NullToZero(Me.I_ShipChg)
If Me.I_PaymentLock = True Then
Else
' Me.I_Payment = Me.I_Total
End If
Me.I_Balance = Me.I_Total - NullToZero(Me.I_Payment)
End Sub
View 1 Replies
View Related
Apr 14, 2005
I have a number of smaller queries that I want to join together to create a super query to display on a report. It works fine when I have a dozen queries joined, but as soon as I add any more, the query takes at least 10 minutes to run (although it eventually works). Is there a limit to the number of queries I can include in a query or is there some other underlying problem?
NOTE : the smaller queries only contain half a dozen records with totals.
Any ideas?
View 5 Replies
View Related
Sep 21, 2004
hi friends,
i have tried had to connect sub type tabels (Saving, Checking, Loan... they have their own ids...) with super type (Account...it has account id...) on the condition of account_type (either "S","C" or "L") attribute in ACCOUNT entity.
how to joint them??? with query or with expression??
i expect help from you.........please.
........thanks.
View 5 Replies
View Related
Jun 15, 2005
i have the following line:
Me.Text2 = Right(Me.Text0, InStr(1, Me.Text0, ".") - 1)
and i would like that if Text0 is "test.exe" i want to return "exe", however, with the above i am getting ".exe" ... can someone tell me what i am doing wrong please ?
Thanks
View 4 Replies
View Related
Jan 4, 2005
I currently have a text box that is calculated off of 6 other text boxes. I want the one field that is calculated to update any time I change any of the other fields. I can do this by putting the same calculation in each ones afterupdate event. Below is one section of my code for the arithmetic, and I want to perform that code many times. Can I use a function to do this, and if so some help would be nice? thanks
Private Sub HEAVY_AfterUpdate()
Total_Sheets = SECOND + HEAVY + LIGHT + SCRAP
Total_Sheets = Total_Sheets * YIELD
Total_Sheets = Total_Sheets + D_Sheets
Total_Sheets.Text = Val(Total_Sheets)
End Sub
View 2 Replies
View Related
Aug 18, 2004
In access, I'm trying to create a database to track material that has an expiration date of one year from the appointment letter date. I'm not sure what I need to do here. Here's what I have: I have a table that has fields for the individual (UNIT), the appointment letter date (Appointment Letter Date), and a True/False field (Out of Date) that I want to make True when the current date is greater than the (Appointment letter date + 365 days). Is this possible through Access tables and/or queries or will I have to incorporate VB? I am an extreme novice when it comes to expressions and query building in Access.
Nathan
View 6 Replies
View Related
Aug 17, 2005
Hi,
I have build a database which work perfectly on most computers. But one or two for the computer are error with the Left, Right and Date SQL function. Is it simplely miss for the install or it a problem with my code?
Thanks
View 2 Replies
View Related
Nov 3, 2006
Hello,
I have developed a MS Access database which uses several functions such as DateDiff and inStr. When I use these function on my machine the database runs fines and the values are calculated.
However the database I have developed is not for my computer and when run on this one computer produce some errors (The date function is mentioned). I believe this is some sort of security issue but I cannot be sure.
Both computers run the same version of Office. Does anyone have any ideas.
View 2 Replies
View Related
Nov 3, 2006
Hello,
I have developed a MS Access database which uses several functions such as DateDiff and inStr. When I use these function on my machine the database runs fines and the values are calculated.
However the database I have developed is not for my computer and when run on this one computer produce some errors (The date function is mentioned). I believe this is some sort of security issue stopping her running the functions.
Both computers run the same version of Access. Does anyone have any ideas.
View 3 Replies
View Related
Aug 8, 2007
Does anyone know of a good resource to mull through all of the built in functions and learn their syntax, uses, etc?
Thanks
bluke
View 2 Replies
View Related
Dec 7, 2007
Is there a list anywhere that tells what all the functions do in access?
I mean functions such as:
RTrim
RTrim$
SaveSetting
Seek
There is a big long list, and some of them.. I have no idea what they do.
View 7 Replies
View Related
Oct 12, 2005
I have a query that contains a field with 4 codes that I need to replace with values.
Example:
Replace([CE020]![LNCH_FLG],"0","None") Replace([CE020]![LNCH_FLG],"1","Free") Replace([CE020]![LNCH_FLG],"2","Reduced")
Replace([CE020]![LNCH_FLG],"3","State Free")
Replace([CE020]![LNCH_FLG],"4"," Paid")
I get an invalid syntax error, I know if I was to have one Replace function it will works fine, but I’ll still have 3 code to convert.
Any Ideas?
Thanks
--pete
View 7 Replies
View Related
Mar 20, 2007
Hi,
I need some help with a function inserted into a query.
I created a function to convert numbers into text. I then created a query to pull some fields from a table. the last field of the query, I inserted the function using the build feature. I was able to see the function in the list of custom functions for the query.
However, when I run the query I get an error message of undefined function - name-. Can I use my function there? or is there some other way to do so.
thanks for any help received.
Regards,
CathyM
View 2 Replies
View Related
Feb 6, 2008
Hi
Im trying to run two count functions on one column in sql (access 2003). I need to return two new column, pass and fail. something like this; select count(grade) as pass, where grade>=40 and select count (grade) as fail, where grade<= 39.
my table is student_ID, module and grade:
student_ID Module Grade
0012 history 59
0034 history 34
0045 maths 78
0031 maths 45
0046 spanish 66
what i need is:
Module Pass Fail
History 1 1
Maths 2 0
Spanish 1 0
I have achieved it by running two seperate querys, one for pass and one for fail and then merging them with another query but is there any other way?
View 1 Replies
View Related
Mar 31, 2008
I have looked and looked at this expression line and cannot find what is wrong.
Repaired: Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(date(forms![enterprise report card].[combo31])),Month(date(forms![enterprise report card].[combo31])),1) And DateSerial(Year(date(forms![enterprise report card].[combo31])),Month(date(forms![enterprise report card].[combo31]))+1,0),1,0))
This gives me an error "Expression entered has a function containing the wrong number of arguements"
However this one:
Repaired: Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0),1,0))
works fine... The only difference is I inserted forms![enterprise report card].[combo31] inside of the Date() function.
Can anyone see what I might be missing??
View 4 Replies
View Related
Sep 23, 2005
I am doing some work on a loan calculator which I have done successfully in Excel. I now wish to do this with an Access form (A2K). Can anyone please confirm that the Financial Functions (ie CUMPRINC etc) that are available in Excel can be used in Access. The Access help menu calls them Worksheet Functions and I can't see any reference to the above function in this forum. I have entered it in a control and get a #name? error. I dont know if I have made a syntax error or that Access doesn't recognise it.The following is the formula I have used =CUMPrinc([rate]/[freq],[nper],[amount],1,[nper],0).
I have not had the same problems with the following formula =PPmt([rate]/[freq],1,[nper],[amount],0)
Cheers
View 2 Replies
View Related
Sep 24, 2004
Hi there,
I`ve got a problem with access. I Have data in several columns like:
Date Value 1 Value 2 Value 3 Value 4
1-1-01 12 10 11 9
2-1.01 14 7 16 11
etc.
Now I would like to add two columns that determine the min and max value of a row. Like below:
Date Value 1 Value 2 Value 3 Value 4 Min_value Max_value
1-1-01 12 10 11 9 9 12
2-1.01 14 7 16 11 7 16
Is this possible with the standaard min function? or how else do i do this?
View 12 Replies
View Related
Mar 10, 2005
I enter this statement on my control source in a text box
of a report for a date field.
=DateAdd("d",1,[startdate]
I get error when I preview the report. I trying to add 1 day
to the date.
Thanks in Advance
View 2 Replies
View Related
May 23, 2006
I need to find the latest of several date fields in a SINGLE record of a query. The Max function seems only to work for a single field across several records in a query. However the worksheet function Max suggests I can use it for in-record fields but it does not work in queries. It is called a Worksheet Function but what is an Access worksheet? I know what it is in Excel but not Access. Neither Help nor two thick reference books even mention worksheets.
View 7 Replies
View Related
