What Does The & "*" Part Of This Query Mean And Do?
May 3, 2007
SELECT [UK Table].[Business Name], [UK Table].[Business Type], [UK Table].Address, [UK Table].City, [UK Table].Country, [UK Table].[Telephone Number], [UK Table].[Website Address], [UK Table].[Email Address]
FROM [UK Table]
WHERE (([UK Table].[Business Name] Like Forms!frmNz!txtBusinessName & "*") Or (Forms!frmNz!txtBusinessName Is Null)) And (([UK Table].[Business Type] Like Forms!frmNz!txtBusinessType & "*") Or (Forms!frmNz!txtBusinessType Is Null)) And (([UK Table].Address Like Forms!frmNz!txtAddress & "*") Or (Forms!frmNz!txtAddress Is Null)) And (([UK Table].City Like Forms!frmNz!txtCity & "*") Or (Forms!frmNz!txtCity Is Null)) And (([UK Table].Country Like Forms!frmNz!txtCountry & "*") Or (Forms!frmNz!txtCountry Is Null)) And (([UK Table].[Telephone Number] Like Forms!frmNz!txtTelephoneNumber & "*") Or (Forms!frmNz!txtTelephoneNumber Is Null)) And (([UK Table].[Website Address] Like Forms!frmNz!txtWebsiteAddress & "*") Or (Forms!frmNz!txtWebsiteAddress Is Null)) And (([UK Table].[Email Address] Like Forms!frmNz!txtEmailAddress & "*") Or (Forms!frmNz!txtEmailAddress Is Null));
I have used this query but fail to know what the & "*" part means. What does it do? What does it mean?
View Replies
ADVERTISEMENT
Dec 16, 2004
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
Thanks
S
View 1 Replies
View Related
Nov 3, 2006
Hi everyone,
I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
Many thanks,
View 6 Replies
View Related
Jul 17, 2007
Hey People,
Is it possible to take out part of a date in a query and put it into a new column, e.g.
if one field is : "Date"... with entries such as 12/05/07.. etc,
is it possible to have another column "Month" that has the specific month in the date column.. e.g the one above will have "May".......
anyone have any idea about this...
cheers.
View 5 Replies
View Related
Jun 7, 2006
Hi All, I am modifying some asp product catalog query to sort by order of 'price' so the data from the DB is displayed on the page from cheapest to most expensive, I am a newbie but keen.....
<%if not request.QueryString("cid")<>"" then%>
</font>
<%end if%> <%
if request.QueryString("cid")<>"" then
set rs = cn.execute("Select * From Products Where CategoryID= " & request.QueryString("cid"))
end if
%> <%
if not rs.eof then
while not rs.eof
%>
This extract shows a request to select products from a specific category but I want to disply them in order of ascending price....can any power users out there set me straight. greatly appreciate it.
cheers now
View 2 Replies
View Related
Jul 7, 2005
Hello all!
I have a customer database and I basically want to find out their geographical distribution. To do this I need to run a query that gives me the sum of customers for each postcode, but this is dependent on only the first few characters of the postcodes (or prefix), eg BH3. The length of the prefix varies between 2 characters and 4 characters with one or two characters followed by one or two numbers.
What I don't want to happen for example is to have postcodes counted as BH1 when in fact they are BH13 or to have postcodes coutned as BH13 when they are actually BH1 3LV.
Does anyone have any suggestions of how I can do this? :confused:
Any help most gratefully received!
:)
View 8 Replies
View Related
Sep 1, 2005
I have a table that I need to identify the records in that have specific text in one of the fields, the field also contains other data. i.e. the field (accessdescription) can contain any combination of the following text (Bridge, Report, Email). and I want to list only the records that have email in this field, noting that the field usually contains at least two of the possible entries.
Any pointers in the right direction would be greatly appreciated.
Thanks
Jubb
View 2 Replies
View Related
Feb 10, 2006
I have a query which calculates Lagtime: which is the difference between the audit date and the current date.
Lagtime: DateDiff("d",[Auditdate],[Date]) and the criteria is >30
this works fine and shows the reports that are over 30 days overdue.
Now I need: There are 2 fields (both are y/no) Minor and Major. Minor need to be answered >30 and Majors with in >7,
how would I write the expression show me Lagtime if over 30 days for a minor and 7 days for a major.
View 1 Replies
View Related
Aug 15, 2007
i have this query that i have several buttons that trigger it. how do i pass over a unique variable with each button. each button needs to append a different event. right now i have it set static to "3rd Party Denial" but i need that to be dynamic based on which button is pressed. PLEASE HELP!!!!!!
Code:INSERT INTO [Status Log] ( Status, Edit_Date, Event, Claim_ID )SELECT [Status Lookup].Status, Now() AS Expr1, "3rd Party Denial" AS Expr3, [Claim Report Info].[Claims Header].Claim_IDFROM [Status Lookup] RIGHT JOIN ([Claim Report Info] LEFT JOIN [Status Log] ON [Claim Report Info].[Claims Header].Claim_ID = [Status Log].Claim_ID) ON [Status Lookup].status = [Status Log].StatusGROUP BY [Status Lookup].Status, Now(), "3rd Party Denial", [Claim Report Info].[Claims Header].Claim_IDHAVING ((([Status Lookup].Status)=[Forms]![claiminformation]![ReportForm]![reportstatus1]) AND (([Claim Report Info].[Claims Header].Claim_ID)=[Forms]![claiminformation]![ReportForm]![Report_ClaimID]));
currently i have a macro running when clicked.
that macro runs 2 quieres and a report.
i'm doing this because thats the only way i know how.
View 7 Replies
View Related
Nov 27, 2013
I need to give the user the ability to select either all of a table say
[tblStores].StoreNo
or only those stores where [tblStores].StoreNo Is Null
I cant for the life of me get my query to accept Null or Is Not Null in the parameter box.
What do I need to do.The end game is to produce a subform to allow users to maintain tblStores
eg
tblStore
StoreNo | StoreNm
000001 | StoreA
<Empty>| StoreB
000003 | StoreC
Null returns
Store B
Is Not Null returns
StoreA
StoreB
StoreC
View 2 Replies
View Related
Jun 26, 2015
I am trying to group records in a query and count them. I have records containing ABC12345
ABC67890
ABE12345
ABE67890
Basically the third letter is what I need to group on so that I can count the number of records with ABC, ABE and any other variant of the third letter but with the numbers all varying all over the place.In the example above I want to find ABC 2 and ABE 2.
View 3 Replies
View Related
Aug 13, 2013
I am needing to compare some information in a way I cant with the normal Unmatched Query.
I have 2 tables with the fields:
| PHONE1 | PHONE2 | FIRST | LAST |
What I need to do is have Table 2 check against Table 1 to see if any of the 'PHONE1' records match 'PHONE1' or 'PHONE2,' and if they do have them remove just the information from PHONE1, not the entire record.
Then I would have to repeat the process to do this with PHONE2. Having it check against 'PHONE1' and 'PHONE2' in Table1.
If both PHONE1 and PHONE2 have a match, the entire record would be deleted. If not, it would keep whichever did not match.
I am currently having to do this manually, because if I just run an Unmatched Query it gets rid of the whole record regardless of if PHONE2 is a good number or not.
If any more information is needed, let me know. Like I said, Im relatively new to working in Access and dont know much.
All of the fields are 'TEXT' type.
Access 2003 on Windows XP.
View 8 Replies
View Related
Aug 13, 2014
I have a table where I have 5 columns, (BatteryL1, BatteryL2, BatteryR1, BatteryR2 and BatterySize).I want to update BatterySize, with a value if any one of the above listed columns contains a specific value using something like 'LIKE' in the query.
For example:
UPDATE Customers
SET BatterySize=13
WHERE BatteryL1 LIKE '%13%'
[code]...
View 3 Replies
View Related
Apr 4, 2013
I have a query with a date field that is formatted mm/dd/yyyy. I have a combo box that is formatted as mm/yyyy. I need the choice from the combo box to be the criteria for the date field in the query. the combo box has to contain the month and year only (which it does now) and the query must return mm/dd/yyyy. I have tried a few statements and the closest I got was a between statement that added 30 days to the combo box selection but that's not really accurate.
View 2 Replies
View Related
Nov 11, 2007
Given a table field that is a hyperlink type.
I need an Update Query to set all records of that table so that the Displayed Value part of the hyperlink field (not the Address part) is set to a particular value.
Any ideas how?
Thanks.
View 1 Replies
View Related
Nov 6, 2012
I'm trying to save a report to my Desktop as a pdf - adding a date {that I am retrieving from a query} as part of the pdf file name.
I get a
Run-time error '2501' - The OutputTo action was cancelled.
(See screenshot).
Here's the code I'm using. So far - all I've tried to do is run the function from the Visual Basic Editor.
Function SaveReport()
Dim strSQL As String
Dim strPath As String
Dim strFilename As String
'Get the ReportDate.
[Code] .....
View 4 Replies
View Related
May 5, 2014
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
View 2 Replies
View Related
Mar 13, 2006
I am having more trouble with the old database I am trying to make.
Can someone have a look if I attach it?
I don't think the ID fields in each of the three tables are working togehter.
Shouldnt they be the same ID number of each record for that person across the three tables?
Thanks.
View 2 Replies
View Related
Aug 8, 2006
I am trying to create a Part Number Database and want it to start at 100000 instead of 1. Can someone tell me how I can get Access to start there?
TIA.
View 4 Replies
View Related
Apr 7, 2008
First time on here and I am looking assistance with the last part of this code. Can someone tell me what this is looking for thanks...
ExlFile.Application.activeworkbook.SaveAs "........Compliance Reports" & Rtn & BU & "-" & Cat & ".xls
View 2 Replies
View Related
Jun 29, 2006
I have a field called images and its name is like abc0001, abc0002 etc, i want to update all the abc to abcd0001, abcd0002 etc, how can I do this using update query or is there any other way to do this, thanks for any help...
View 1 Replies
View Related
Jul 23, 2005
On a report I have a field that has a value that can be anywhere from 15 to 25 char. Can I set up the report field to only display the 1st 10 char. ?
jon
View 9 Replies
View Related
Jan 16, 2006
I have a data base with the field named "PartNumber" this is a 13 diget number. How can I search on the last 7 digets only
View 2 Replies
View Related
May 27, 2007
Hello. Im new here and also new to both Visual Basic and Access so please be patient with me :)
I've downloaded SearchDB.zip uploaded by agehoops. Its works exactly like its supposed to but I need to update it a bit.
Im making a MovieDatabase and have a table with Orginaltitle, SwedishTitle, Director, Actors and so on. When I search in "Director" for "Francis Ford Coppola" or just "Francis" the movies which is related to him comes up. But when I search for "Ford Coppola" or only "Coppola" nothing comes up. I want the user to have that possibilty to search using the last part of the name too. Is that possible?
When searching with the binocolor icon it works fine when chosing "part of record", i think thats the english translation for "Del av fält". I have a swedish acces version so im not so sure but anyhow I think you understand what I mean.
To sum it up I want the ability to search writing only a part of what the record contain including the last part and not only the first part like it is right now.
Appreciate any help!
Regards,
SoloJuve
View 6 Replies
View Related
Feb 13, 2008
Excel in Access (Part 1) (http://www.access-programmers.co.uk/forums/showthread.php?p=671226)
Excel in Access (Part 3) (http://www.access-programmers.co.uk/forums/showthread.php?t=143970)
Video Version HERE: (http://www.access-programmers.co.uk/forums/showthread.php?t=144045)
How to use the Normalization Form (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)
In Excel in Access (Part 1), we went from this:
http://i185.photobucket.com/albums/x317/UncleGizmo/StudentTableBoolean_2.png
To this:
http://i185.photobucket.com/albums/x317/UncleGizmo/StudentTableTransposed2.png
This was achieved with a form based tool available to DOWNLOAD here. (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=20619&d=1202664804)
See instructions on its use here: (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)
However you may be looking at the new resultant table and wondering what on earth to do with it. I hope to take you through the process of making it into something useful in this thread.
The first thing you will notice is that where the check box is not checked, then that whole row is redundant, for instance there’s no need to record that ID number “1” --- “Has Not” taken Maths, English, Geography, Physics etc, it would suffice just to record the subjects that have been taken , In this case Biology, PT and Social. Looking at those entries in particular, then a general rule of logic can be defined, “delete all the rows where the check boxes are false”.
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1b.png
Once you have deleted all of those rows,
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1c.png
then it becomes obvious that the check boxes themselves which now “All” contain a true value are also redundant, they can be deleted just leaving you the text entry identifying the subject taken by each student.
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1d.png
Using the “relational” properties of the database that is one more thing you can do which will improve efficiency and that is to replace each text entry --- Maths, English, Geography, Physics etc, with a number linking that field to a look up table.
First of all you need to create a look up table; this can be done by applying a create table query to extract just the unique values for the “subject” There is a video showing how to do this here: (At time index 1min) (http://www.viddler.com/explore/TonyHine/videos/67/fullscreen)
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1e.png
This unique list should be called “tblSubject” this table is not quite finished, you need to add an identity column to the left of the text representing the individual subject, this identity will then appear in the previous table.
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1f.png
Once you have completed the “look up table” you then need to replace the entries in the student subject table “tblStudentSubject” where it shows subject in text form with the number representing the link to the look up table. This is the query:
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1g.png
And here is the new column created:
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1h.png
This way your design changes to the table are making it much more efficient, holding the same information but with less data.
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1k.png
View 4 Replies
View Related
Feb 19, 2008
Excel in Access (Part 1) (http://www.access-programmers.co.uk/forums/showthread.php?p=671226)
Excel in Access (Part 2) (http://www.access-programmers.co.uk/forums/showthread.php?t=143607)
Video Version HERE: (http://msaccesshintsandtips.ning.com/profiles/blog/show?id=948619%3ABlogPost%3A7031)
How to use the Normalization Form (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)
Using the normalization form --- Download Here (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=20619&d=1202664804) we converted part of a spreadsheet type table into the beginnings of a relational table.
From this table we derived a “look up table” now giving us a total of three tables, the remains of the first table, “tblStudent” (the student names), the next table “tblStudentSubject” stores the subject(s) related to each student, and finally a third table, “tblSubject” a “look-up table” to store the actual subject description.
http://i185.photobucket.com/albums/x317/UncleGizmo/ThreeTables.png
From the table “tblStudentSubject” we created a form in datasheet view:
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm.png
Now all we need do is combine this datasheet view form with a form based on the students list, and this will give us a form for correctly displaying the student names and the subject(S) the student is taking in one Form:
Using the wizard create a basic form from the student table and name it “frmStudent” arrange its size so it has some open space as shown.
http://i185.photobucket.com/albums/x317/UncleGizmo/StudentForm.png
Now open “frmStudent” in design view
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_1.png
And drag the subform “sfrmlStudentSubject” into the clear area on the student form “frmStudent”
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_2.png
You may wish to delete the text box, you don’t have to but I usually find it looks better without it.
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_3.png
Size the form to suit
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_4.png
Now save the form and have a look, you will notice that it incorrectly shows all of the records in the subform,
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_5.png
Now the next bit is tricky, for two reasons, the form isn’t really on top of the other form, it actually sits in a subform window and you need to gain access to the properties of this subform window by clicking on the tiny line that you can just see around your subform. This can be a difficult task to master first time.
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_6.png
Now open the “subform field linker” dialog box by clicking on the ellipsis (…)
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_7.png
Phone: +44 1635 522233
Mobile: +44 7747 018875
Email: email@tonyhine.co.uk
Web: http://msaccesshintsandtips.ning.com
View 1 Replies
View Related