Having A Hard Time With LIKE Operator

Sep 3, 2005

I want to create a query that extract rows that has a specific word.

i.e.

select * fro table where field like "*ART*"

however it should not retrieve "DART" or "ARTI"

i tried using the following [!a-z] and * like this:

SELECT * from DLs where DLName like "*[!a-z]ART" or DLName like "*[!a-z]ART[!a-z]*" or DLName like "ART[!a-r,s,!t-z]*" or DLName like "ART"

it is very long and complicated - cant i cover it all with one like ?

Also the fact that i also want it to get ARTS (i dont know how to say !a-z expect 's')

Thanx in advance

View Replies


ADVERTISEMENT

Hard Code TIME To Selected Date On Form (to Make It Date&time) For My Query Criteria

Aug 17, 2006

Hello buddies :D, do you have any idea how to make this work?

To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.

This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif

View 10 Replies View Related

Populate Combo Box From Values Through Query And Hard Coded Value At Same Time

Jun 18, 2015

Currently I have a combo box in my access 2007 application which is drawing its value through a table using a SQL Query. I would also like it to show up a hard coded value in addition to the current list.

View 3 Replies View Related

Too Hard For Me...Help

May 16, 2006

hi all,
I'm just beginning to know the world of Access so maybe the solution for my problem is pretty simple.
I have a huge database with Itemcodes, Costprice of materials and EffectiveDate (date on which new prices are valid). These dates represent past dates as well as future dates. The one that interests me the most is the most recent date in the past.
What I would need is a query that filters out the most recent date per itemcode because right now there are several prices with several validity dates. Eventually it should result in a database that shows an item with one validitydate and one price.
Right now the same itemcodes are repeated because different validitydates are related to it.
I really hope somebody can help me out on this.
Thanks in advance.

View 2 Replies View Related

Hard For Me, Easy For You!

Jan 17, 2006

I really have got a mind block!

I am creating a small DB for a vast CD collection and have 3 related tables at present.

I want to have 1 table with the artists, but have it updated when I add a new artist on the main table/form.

So the form does not have the artist. I add that artist, and it is added to the artist table for future use.

Sure I can do this, but cant rememebr how. Please help :)

Jay

View 3 Replies View Related

Need Help With Hard Query

Jul 8, 2005

My DB has a table named noldba.int_member_history, which contains the following fields:
member_id,
date_start,
date_end,
member_status,
iva_case_id,
case_id,

It is possible for the member to have several lines of data under their same member_id like this:

member_id date_start date_end member_status iva_case_id case_id

20125626 12/1/1990 2/31/1990 N 21116625 20002717C
20125626 1/1/1991 4/30/1992 A 62574431A 20002717C
20125626 5/1/1992 8/31/2000 N 20006625 20002717C
20125626 9/1/2000 12/31/9999 M 21116625 20002717C
20125626 8/1/2000 12/31/9999 M 20111832 20090698C

So this member had 4 start and end dates under case 20002717C
and only 1 start and end date under case 20090698C

I'm trying to write a query which is looking for member_id's along with a case_id which only has one member_status returned from the query and the
member_status must be M. In this case the member has two cases which have the M member_status, but case 20002717C has other types of member status so I would not want the query to return the 20002717C case, only the 20090698C
I'm sure Im asking for something that can't be done...but I'd never know unless I asked. Thanks

View 1 Replies View Related

This Shouldnt Be Hard.

Sep 6, 2005

I need a query that will return values of a customer number that have been created within the last week.

There is a customer_code field and a date_created field.

Any help would be appreciated.

View 7 Replies View Related

Year Only -haw Hard Can This Be!!!

Aug 24, 2006

I want a field in a table that is a date but that is the year only.
I only want this because I want to be able to filter my charts to show one or a span of years for comparison. I have criteria in a query that states between [forms].[charts].[begin] and [forms].[charts].[end] and the corresponding form that my user puts the desired begginning and ending dates into before pressing buttons that bring up the charts based on the query.

My problem is that I can't even get the table to take a year only date. If I leave the date field as text the between clause in the query won't work. I tried it as a number with >= etc. and that didn't work either.

How hard can something this simple be?

Please help me oh cyberspace wizards.

View 9 Replies View Related

Simple But Hard If You Dont Know

Jun 10, 2005

I have created a database in which I have three Fields.
1. Date Joined
2. Renewal Date
3. Member/Renewal

When I type in the date a person joins our club, I have made the renewal date come up displaying a year ahead, when renewal of membershiip is due.

I would like to be able to put in the date joined date and have the field 3 show Member and then when the renewal date is reached, to have in automatically change to Renewal.

It is probaly a simply formula but I can not get it right.

Please Help.

Foxy

View 6 Replies View Related

A REALLY Hard Query Question

Aug 12, 2005

Hi,

Currently I have one query that prompts the user for a beginning date, and end date of a week. It then proceeds to show the user all records in that time frame and makes a table. Now I need another query that looks at the dates the user had entered and gets all the records from the week before the user's dates. This query would also be a table query, but separate from the first one.

If anyone at all is able to help me out, I will be deeply impressed and eternally grateful! :)

Thanks,
Chris C.

View 12 Replies View Related

Help With A Query... Hard To Explain

Mar 29, 2008

Hi all!, i would really appriciate it if you could help me out. I have this complex query... in one table i have dates and costs associated that show when fuel was put into the system

such as:
1/1/2008 -- $200
1/20/2008 - $100
1/25/2008 - $150

I then have another table that has data when fuel was taken out of the system
such as:
1/2/2008 -- ($50)
1/3/2008 -- ($20)
1/4/2008 -- ($10)

what i need is a query that is able to show a running tab on the fuel tank and how much fuel is in it...

so for example if it could spit out a result such as
1/1/2008 -- $200
1/2/2008 -- $150
1/3/2008 -- $130
1/4/2008 -- $120

If this is convoluted i can upload a little database..

Thanks! (Here is the current query i am using but its not working right.

SELECT DatePart("yyyy",[purchase_Date]) AS AYear, DatePart("m",[Purchase_Date]) AS AMonth, DatePart("d",[Purchase_Date]) AS ADay, DSum("gallons_purchased","purchase_fuel_tbl","DatePart('d', [purchase_Date])<=" & [ADay] & " And DatePart('m',
[purchase_Date])<=" & [AMonth] & " And DatePart('yyyy',
[purchase_Date])<=" & [AYear] & "") AS RunTot, fuel_use_tbl.fuel_date, fuel_use_tbl.external_ID, fuel_use_tbl.gallons, [RunTot]-[fuel_use_tbl].[gallons] AS cur_Value
FROM (tank_tbl RIGHT JOIN purchase_fuel_tbl ON tank_tbl.tank_ID = purchase_fuel_tbl.tank_ID) LEFT JOIN fuel_use_tbl ON tank_tbl.tank_ID = fuel_use_tbl.tank_ID
GROUP BY DatePart("yyyy",[purchase_Date]), DatePart("m",[Purchase_Date]), DatePart("d",[Purchase_Date]), fuel_use_tbl.fuel_date, fuel_use_tbl.external_ID, fuel_use_tbl.gallons
ORDER BY DatePart("yyyy",[purchase_Date]), DatePart("m",[Purchase_Date]), DatePart("d",[Purchase_Date]);

View 14 Replies View Related

Unique Hard Drive Serial

Dec 8, 2005

I have seen some API that gets the serial of the hard drive, but this information is not unique and changes when the volume is formated.
Does any one know of a way to return the orignal manufucture's information/serial which is unique(GUID) to the volume.
Any Idea will be highly appreciated.

View 6 Replies View Related

Hard Coded Dymanic Fields

Feb 6, 2006

Having one of those days!!!

I have a simple query populating fields from 2 tables and would like to add a further field to my query (not in tables)

In this field I wish to hard code in 2 values. Can anyone suggest the best way of doing this?

Thanks

Paul

View 1 Replies View Related

Two Versions Of Access On My Hard Drive

Nov 28, 2006

Would having Microsoft Access 2002 and 2003 on my hard drive cause me to have error with my code?

View 4 Replies View Related

Easy Question, But Hard To Figure It Out Lol

May 9, 2005

Hey guys,

this may seem unprofessional but anyway..

does anyone know how to DELETE one or more realtionships ?? :o

View 6 Replies View Related

Hard Links In Split Database

Oct 11, 2004

I have a split database on the network, but I believe it contains hard links (i.e. H:path odb). So, if others use it, if they don't have their H drive mapped the same way I do, they'll get errors. How can I fix this so that it maps to the full path (i.e. \path odb)?

View 1 Replies View Related

Saving To External Hard Drive

Sep 30, 2014

I've saved my Access files (as well as my Word files) to an external hard drive. I can open any Word doc from the hard drive, but when I try to open an Access file, it gives me the following error message: "can't find Language DLL msain.dll". What does that mean, and how can I fix it so that I can open an Access file from the hard drive?

View 1 Replies View Related

Wireless Network Hard-drive Back-End

Mar 18, 2006

Does anyone have any experience with placing a multi-user back-end access database on one of these inexpensive "external network hard drives"?

I have a situation where I'd like to share my database with multiple users, but the corporate IM rats have pretty much eliminated all means of allowing us lowly employees of doing that with their network. We have no file server. We do have sharepoint services, but we only have Access 2000, which as far as I know is not capable of exploiting the sharepoint/xml back-end database features. Also, no one in the office here can share a directory on their workstation because they won't grant us administrator priveledges.

So thats how I came upon the idea of buying or creating cheap (<$250 USD) wireless network hard-drive on which to put the back-end. I'm mainly interested in knowing if it will work, and that the performance isn't going to be absolutely pathetic. I'm only looking at having 3 or 4 users at any one time, and the entire database will only house a couple thousand records.

View 3 Replies View Related

How To Replace Hard Coded Path Of Current Db

Aug 2, 2006

I have something like this
Code:'sets generic workspace Set ws = DBEngine.Workspaces(0) 'sets target database from workspace ******NOTE hardcoded path****** Set db = ws.OpenDatabase("E:Status ReportstatusReport_Test.mdb") 'sets Recordset to deliverablePercentComplete sql statement for target database Set rs1 = db.OpenRecordset(deliverablePercentComplete)

is there a way to replace the path in the set db call, basically it is the current database that is being worked on, thus it is the current database that is opened, is there a way to say something like...

set db = ws.OpenDatabase(currentDB)

View 1 Replies View Related

Question On How To Grab An Excel File On The Local Hard Drive

Jun 15, 2006

Hello, I was needing some help coming up with the code to search the local hard drive and grab a file and then import it into an Access table. I'm trying to do this on a form if possible. If you know of another way, that would work. Any help would be appreciated. Thanks.

View 9 Replies View Related

Modules & VBA :: Replace Hard Code Directory Path With Textbox

Jun 17, 2013

How do I direct the ZipFilePath and ZipFolderPath to use a directory I define in a textbox in a form? Instead of hard code like it is now?

Option Compare Database
Option Explicit
Public Sub NewZip(ZipFilePath)
On Error GoTo Err_NewZip
Dim lngFile As Long
lngFile = FreeFile

[Code] ......

View 2 Replies View Related

Queries :: Hard Coding In Field Name In Query Design View?

Jan 23, 2014

I am trying to hardcode in the field name. This is what I want to hardcode "TEXT(ROW(A17),"-0")" but the parenthesis and quotation marks are causing a syntax error. It appears like this

IDENTITY:"TEXT(ROW(A17),"-0")"

What am I doing wrong?

View 7 Replies View Related

BETWEEN...AND Operator

Feb 6, 2006

Hi

I have been refreshing myself with SQL on Access 2002. All was going well until I got to the BETWEEN...AND operator. Using the Northwind sample database I was writing the query on the employee table to select all the columns between 'Fuller' and 'King' :

SELECT * FROM Employees
WHERE LastName
BETWEEN 'Fuller' AND 'King'

However this only brings back the two rows for 'Fuller' and 'King' whereas I would expect at least 5 rows or more depending on whether access would count 'Fuller' and 'King' as inclusive.
Have had a look on the microsoft office help page but seem to be doing everything correct so I am wondering why it still doesn't work? No doubt simple but its concealed in mud at the moment. Any help apreciated.

cheers

View 13 Replies View Related

Info About 'like' Operator From Help

Sep 26, 2005

I am new to this forum, so forgive me if this question has been asked before

I needed exact information about the way the 'like' operator works, so I pressed F1, typed 'like' in the "Answer Wizard" and was told to "rephrase my question". :eek:

The index was slightly more helpful as it obviously contains 2 topics with 'like_operator', but choosing them does not reveal anything. :confused:

Where can I find info about such things?
I also need to use an operator called 'contains' which I have used when working with SAS, but appearently it does not exist in Acccess or it has another name. Has anybody heard about it?

View 2 Replies View Related

MINUS Operator

Nov 15, 2005

Hi guys!

It's unknown for me why Access hasn't got MINUS implemented and Unmatched Query Wizard doesn't help me much. So I'm wondered if anyone had to confront the following task:

Table1

Book Agent
----------
b1 a1
b2 a2
b3 a3

Table2

Book Agent
----------
b1 a1
b3 a3

The problem is I have to eliminate duplicates but I need to find unique row, which is Book and Agent. From my example it's row with (b2 a2). I tried using
Select * From Table1
Where (Book, Agent) Not In (Select Book, Agent From Table2)
didn't work
I also tried EXISTS
didn't work
Unmatched Wizard compares only one field, not two.

Any comments welcome, thank you

View 2 Replies View Related

NOT Operator Question

Jan 10, 2007

I have an invoice-line table with records for every line on invoices. I'm trying to return all the lines associated with certain invoices, excluding all lines with "FREIGHT" in a line item field. I created a query that lists all the invoice numbers for which I want to display invoice lines for. I created a new query with the invoice line table linked to the invoice number query by invoice number (a one to many relationship). There are about 1400 results from this query. By making the criteria of the line item field "FREIGHT" and running the query, I can see that there are 81 "FREIGHT" records that I ultimately want to exclude. However, by typing NOT "FREIGHT," it appears that not only are the "FREIGHT" line item records excluded, but also records with a blank (null)line item field are excluded, and I end up with about 650 results. What's going on here? Why is the NOT "FREIGHT" critera also excluding null records? I don't have any other criteria specified in the query. :confused:

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved