Queries :: Return Word Unknown For Any Code That Doesn't Have A Match

Aug 20, 2013

I have lookup table I use to return names for various "Sales Class" codes.It all works good but if there is a code that isn't in the lookup table it leaves that field empty.I want it to return the word "Unknown" for any code that doesn't have a match.Here is the SQL:

Code:

SELECT [Data1].OrderNum, SalesClasses.[Name]
FROM [Data1]
LEFT JOIN SalesClasses ON [Data1].[Sales Class] = SalesClasses.[Code1];

Table examples:

Data1:
OrderNum - Sales Class
111 - class1
222 - class2
333 - classX

[code]...

View Replies


ADVERTISEMENT

Queries :: Return Results That Exactly Match Criteria

May 9, 2013

I have 3 main tables: tblEmployees, tblJobs, and tblProcedures. (See attachment for relationship diagram and additional supplemental tables).A job can have multiple procedures and an employee can have multiple procedures too.

I need to write a query such that when searching by a specific job I can see all of the employees who are qualified for that job. This is done by seeing which employees have the procedures that belong to a job. But here's the catch: since a job can have multiple procedures, if an employee only has some of the procedures I don't want that particular employee to return as a search result. The employee must have ALL the procedures that belong to the selected job.

So for instance if I have:

tblJobs
Job1
tblEmployees
Emloyee1
Employee2

[code]...

If I search by Job1, I want only Employee2 to return as a result, NOT Employee1.I am at a lost for how to construct the SQL for something like that.

View 4 Replies View Related

Queries :: Return Records With Partial Match

Feb 14, 2014

I understand that a query can accept wildcards in the search criteria but this is different.

I have a form on which a user selects a [product] part number in an unbound combo e.g. "12345-V111".

I then need a subform on the form to list records where the relevant field matches the left most part of this part number but is not necessarily complete. e.g. records with "12345-V1" would be returned, records with "12345-V2" wouldn't. The number of characters recorded in this field will vary, but if the characters that are entered match, then the record should be listed.

In other words, the record entries themselves need to be treated like wildcard entries.

View 7 Replies View Related

Queries :: Match Postcodes Ad Return Part That Matches?

Sep 7, 2014

I have been given a list of UK postcodes, with the following format L15TG or TS14TGU.

I need to be able to match these postcodes to a list of postcodes I have stored in the database, however, my list are only UK outcodes, so L15TG is just L1 and TS14TGU is just TS14.

So I need to match the records and return the part of the string that matches i.e take L1 from L15TG.

View 5 Replies View Related

How To Match 4 Character Word Or Number Or Combination By Identical 4 Characters Word

Apr 11, 2013

How to match 4 character word or number or combination by identical 4 characters word or number or combination in one word have 10 or 15 characters.

I have to two separate tables (Table A and Table B). Table A has one column (Tag No) and Table B has about 15 columns with one column name Tag No as below

Table A

Table B

Tag No

Tag No

2009

ZZZ-2030-DC

2010

ZZZ-2010-M9P

[code]....

They're both in MS Access.I am trying to match 2 tables - columns (Tag No) with join query, but not success. I want to match 4 characters in Table-A with 4 similar characters in Table-B (Tag No) cell.What query is suitable to compare two tables.

View 8 Replies View Related

Number Of Records Doesn't Match

May 3, 2005

(this might be more appropriate in Forms; apologies if I'm putting it in the wrong place.)

My company runs a home repair program and we keep track of our clients in a database I made a few months ago. We use almost exclusively a form drawing from 3 tables. We just hit 800 records (according to the form) and I was checking some things, and found that there are 807 records in each of our 3 tables.

I did a preliminary check, and the primary keys match as best I can tell, so I'm trying to figure out how 7 records can be missing from our form.

Any ideas?

Thanks in advance.

will

View 3 Replies View Related

Query Needs To Return Match And Unmatched Results

Jul 27, 2006

I have a query pulling data from several tables. In the middle I've got join between a part number in a BOM table and a part number in an inventory table.

What I want is the query to return the inventory location when we have stock. When we have nothing in, then there would be no entry in the inventory table and it should return a blank or null in this and all the proceeding tables.

Can this be done?

View 3 Replies View Related

Ctrl+F Doesn't Return No Find

Apr 25, 2005

I've been using the ctrl+F search in order to search my db for a string or phrase (from a form). Now, after I put it on the Intranet and accesss it through there, I use ctrl+F and if I search for something that is not there, it doesn't return a "did not find" box. It's like it just keeps searching.

Is using the find feature like this just bad form and I should write queries for users to search the db?

Comments/critiques/criticisms welcome. Thanks.

View 1 Replies View Related

Query Doesn't Return Any Fields

Mar 7, 2007

I created a database that contains 2 tables. I then created a query that simply groups the data and calculates a row total. And it works as expected. However, when I try to create a report, using the query, I get, "The wizard was unable to generate fields from the record source you chose. Perhaps you chose a query that doesn't return any fields."

What am I doing wrong?

Screencaps attached.

Thanks.

View 9 Replies View Related

Sum() In Empty Table Doesn't Return Zero

Jun 12, 2007

i have a query with this basic structureSELECT id FROM table1,(SELECT SUM(field2) as total2 FROM table2) - (SELECT SUM(field3) as total3 FROM table3) as totalto understand better let's say that:table1 are clientstable2 is money that enters for clientstable3 is money that exits from clientsi want to obtain a balance. the problem is that if table2 or table3 has no records for certain client, this client is exluded from resultsany ideas?i tried using ISNULL like this:SELECT id FROM table1,ISNULL((SELECT SUM(field2) as total2 FROM table2),0) - ISNULL((SELECT SUM(field3) as total3 FROM table3),0) as totalbut it didn't workedthis is the querySELECT id, UPPER(apellido_titular) + ', ' + nombre_titular AS padre, (SELECT SUM(facturas_items.importe) AS totalf FROM facturas INNER JOIN padres ON facturas.id_padre = padres.id LEFT OUTER JOIN facturas_items ON facturas.id = facturas_items.id_documento WHERE (padres.id = p.id) AND (facturas.fecha_vencimiento < GETDATE())) - (SELECT SUM(recibos_items.importe) AS totalr FROM padres INNER JOIN recibos ON padres.id = recibos.id_padre LEFT OUTER JOIN recibos_items ON recibos.id = recibos_items.id_recibo WHERE (padres.id = p.id)) AS totalFROM padres pWHERE (activo = 1) AND ((SELECT SUM(facturas_items.importe) AS totalf FROM facturas INNER JOIN padres ON facturas.id_padre = padres.id LEFT OUTER JOIN facturas_items ON facturas.id = facturas_items.id_documento WHERE (padres.id = p.id) AND (facturas.fecha_vencimiento < GETDATE())) - (SELECT SUM(recibos_items.importe) AS totalr FROM padres INNER JOIN recibos ON padres.id = recibos.id_padre LEFT OUTER JOIN recibos_items ON recibos.id = recibos_items.id_recibo WHERE (padres.id = p.id)) > 0)ORDER BY UPPER(apellido_titular) + ', ' + nombre_titularsorry for my poor english

View 3 Replies View Related

Modules & VBA :: DLookup Doesn't Return Correct Value

Aug 3, 2015

I'm trying to use a DLookup to get a specific value from a field in a table.

This is what my code currently looks like;

Code:
JOBID = DLookup("[ID]", "MASTER PLANNER", "[JOB NUMBER] = '" & JOB_NUMBER & "'")

Basically I want to get the ID (a number) from a specific record where the JOB NUMBER equals the string I have typed in to a field on the form, also called JOB NUMBER.

However, my problem is that it doesn't navigate to the record where the criteria matches, it just chooses the ID from the very first record of the table.

what I'm doing wrong?

View 9 Replies View Related

My Code Doesn't Do What I Want It To Do

Jan 19, 2006

Hello All,

I'm trying to display a choosen record from a table with a record id that is a TEXT data type. A variation of this same code works correctly in another form where the record source is a table with a record id that is a AUTONUMBER data type.

The below code (Access 2000) displays the first record in the table.

Private Sub cmdTypeCodeSearch_Click()
On Error Resume Next

Dim strtyTypeCodeID As String
strtyTypeCodeID = GettyTypeCodeID

'Just put this in to verify record id
MsgBox "You selected " & strtyTypeCodeID & " for your record key"

If Not IsNull(strtyTypeCodeID) Then
Me.RecordsetClone.FindFirst "[tyTypeCodeID] = " & strtyTypeCodeID
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Record Not Found"
End If
Me.Refresh
End If

End Sub

Any guidance would be appreciated.

Dale

View 8 Replies View Related

Queries :: Append Query - INSERT INTO Statement Contain Unknown Field

Sep 19, 2013

I have two tables each with an ID field (autonumber/PK/No Dup etc).

I want to append two fields from one table to the other table. I have set up an Append Query to do this but it won't work - I get the following error - "The INSERT INTO statement contains the following unknown field: 'FiID'...."

View 2 Replies View Related

Trying To Figure How To Count How Many Times A Word Has Been Used And Return 0 If Not

Sep 23, 2005

Trying to figure this out, but I’m not getting anywhere.

I have 2 tables one is just a list of options that can be used in a drop down box in the other table.

What I am trying to do is create a query that will count how many times the words from the first have been used in the second query. I have been able to get it to count how many times the word has been used but I also need it to return a 0 if the word has not been used.

In my query I used both tables to try to do this but it only returns the ones that have been used.

I will attach the DB if someone is willing to take a quick look at it to see if they can get it to work.

Any help will be greatly appreciated.

Thanks in advance

Jon

View 2 Replies View Related

Tabbed Pages - Code Doesn't Seem To Work

Nov 13, 2006

I'm using a 5-tabbed form and have put it in my detail section.
Within the on click event of one tab knob i would like to activate some knobs in the form header where the tab resides on.

I've coded everything properly but in one or other way access doesn't seem to communicate between the tabbed form within the details and the form header. Anybody an idea?

View 4 Replies View Related

Modules & VBA :: Code Doesn't Wants To Set The Control Default Value

Aug 7, 2013

code doesn't wants to set the control default value

View 5 Replies View Related

Modules & VBA :: Repaint Doesn't Work Until End Of Code

Jul 31, 2015

I have a process which runs lots of slow append queries, so I want to reassure the user of progress, by changing the font colour of labels to green when the relevant queries have finished. This is the code I'm using

Code:
'Update the older data if that option is selected
If Me.Menu_YearOption.Value = 1 Then
DoCmd.OpenQuery "2-10 Append FY1112"
Me.lbl1112.ForeColor = 32768
Me.Repaint
DoCmd.OpenQuery "2-12 Append FY1213"

[Code]...

Each query takes about a minute to run, as it gets data from a sharepoint server, but the labels don't turn green one at a time as expected. Instead, I get the spinning wheel until all the queries have run, then all the labels turn green together.

Is there something else I should be doing rather than Me.Repaint ?

View 11 Replies View Related

VBA Code Doesn't Work After Saving In ACCDE

Oct 2, 2014

I saved my front end db as .accde in order to distribute it without user being able to edit my objects. As it turned out, when I open the .accde now I see my code doesn't work anymore. I have a lot of startup code and now it doesn't trigger at startup. Basically all my forms use vba code and none of them works in .accde . Accdb version works without any issue.

View 3 Replies View Related

Entering Field Value With Code Doesn't Trigger New Record With AutoNumber

Jan 4, 2006

In trying to respond to another thread, I have run into something that is confounding me (or maybe I'm just getting dense).

We have a subform. One field has an event on DblClick to launch a search form. When the user identifies the target, he/she clicks a button on the subform. This pushes the appropriate value into a field on the original subform using VBA code and closes the search form. This all works fine.

The behaviour that is driving me bugging is when the user clicks on a new record (i.e. new line) on the subform, we would like to automatically generate the next record (E.g. when you type in a field of a record with autonumber in datasheet mode, Access automatically generates the next record). Currently this doesn't happen - Access generates the PK for the record being modified, but doesn't generate the view of the next record.

What really confuses me is that I have created similar looking example in which this works just fine. I can't figure out which of the differences between the two samples is causing this behaving.

Also, typing information into the field on the subform does cause the next record to be generated. It is just doing this via code that works in one case but not another.

I have narrowed it down to the actual subform. Even as a standalone form the form exhibits the same behaviour.

For reference, the original thread is
http://www.access-programmers.co.uk/forums/showthread.php?t=99457

Any suggestions?

-grommit

View 6 Replies View Related

Modules & VBA :: Office 2010 / Click Button Event Doesn't Run Code

May 27, 2014

In the Main Form , I have few buttons and I am writing the following code to open the right form when a button is clicked but unfortunately the code isn't doing anything. No form is opening. I am using MS office 2010.

Code:
Private Sub BtnOption_Click()
DoCmd.OpenForm "Form1", acNormal, , , acFormAdd, acDialog
End Sub
Private Sub Form_Load()
'Me!Label1.Caption = "Welcome " & Environ("username") & "!"

[code]....

View 14 Replies View Related

Credit Code Get No Return ?

Oct 1, 2006

Can some one help me with this code, I get no return, ???

. Private Sub cust_credit_score_1_AfterUpdate()

If [Forms]![Customer Form]![cust_credit_reply_1] = " [Bad Credit]"Then
[Forms]![Customer Form]![cust_credit_score_1] = 5

ElseIf [Form]![Customer Form]![cust_credit_reply_1] = "[PoorCredit]"Then
[Forms]![Customer Form]![cust_credit_score_1] = 10
Else
[Forms]![Customer Form]![cust_credit_score_1] = 15
End If

End Sub


Thank You For Your Help

Johnny C.

View 14 Replies View Related

Modules & VBA :: SQL Code Return Value And Set To Variable?

Sep 4, 2013

Code:
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim SQLstr As String

[Code]....

I created this about 1 hour ago but my laptop crashed and didnt save. So, I open a record set and rs is now loaded with the record I want,

how do I assign the value of "Status" as a vba variable. when I try StatusInt = rs I get the "Type Mismatch" error...

View 4 Replies View Related

Modules & VBA :: Code To Return Field Name Rather Than Value

Apr 17, 2014

I have a code that finds the first, second, and third minimum value in a row across the fields. Now, I am trying to find a code to look at these values, find the field it is located, and return the field name. I tried several variations of my code to return the field name rather than the value, but have been unsuccessful to this point.

Function NthMinimum(intPosition As Integer, ParamArray FieldArray() As Variant) As Variant
Dim varTempArray() As Variant, varTempValue As Variant, intArrayValues As Integer
Dim I As Integer, J As Integer
ReDim varTempArray(UBound(FieldArray))
intArrayValues = 0

[Code] ....

As you can see, this works to find these values while ignoring NULLS. How to return the field name?

View 1 Replies View Related

Multiple Word Search In Code

Apr 17, 2008

Hi everyone. I have the following code attached to a text box.

Private Sub txtsearch_AfterUpdate()
Me.lstsearch.RowSource = "Select [R&D ID#], [SKU#], [Project Name], [Construction level], [Manufacturer], [Hobbico Status], [R&D Work By], [Product Manager], [Desktopper]" & _
"From [Project Main]" & _
"Where [Project Name] like '*" & Me.txtsearch & "*'" & _
"OR [SKU#] like '*" & Me.txtsearch & "*'" & _
"OR [R&D Work By] like '*" & Me.txtsearch & "*'" & _
"OR [Product Manager] like '*" & Me.txtsearch & "*'" & _
"OR [Desktopper] like '*" & Me.txtsearch & "*'" & _
"OR [R&D ID#] like '*" & Me.txtsearch & "*'"
Me.lstsearch.Requery
End Sub

This code searched for matching text strings in a few different filed. It is working really well, but it would become much more helpful if instead of searching one single text string, I could enter two in the same box and it would perform the search based on matching both strings, that is, list all the records that have xxxx and xxxx in any of the fields checked.

Can the above code be tweaked to do this?

mafhobb

View 2 Replies View Related

How Can This Code Be Modified To "Match Case".

Dec 7, 2007

I'm using the code below to link criteria in my DB by double clicking a text box in the main form. The code is set to search and it works, the only thing is that it will return fields with part or the whole criteria set to search for. The code has a wild card segment. I'd like the code to return only an exact match. How can I change this code to "Match" case only.
any help is appreciated.
Thank you
Code:DoCmd.OpenForm "frmTP", , , "[Right Front] & ' ' & [Left Front] & ' ' & [Right Tag axle Outer] & ' ' & [Right Tag axle Inner] & ' '& [Left Tag axle Inner] & ' '& [Left Tag axle Outer] & ' '& [Right Rear Outer] & ' '& [Right Rear Inner] & ' '& [Left Rear Inner] & ' '& [Left Rear Outer] & ' '& [Right Rear Rear Outer] & ' '& [Right Rear Rear Inner] & ' '& [Left Rear Rear Inner] & ' '& [Left Rear Rear Outer] & ' ' Like '*2371" & Me.BarCode & "*'"

in the form that opens the record source has this filter.
Code:[Right Front] & ' ' & [Left Front] & ' ' & [Right Tag axle Outer] & ' ' & [Right Tag axle Inner] & ' '& [Left Tag axle Inner] & ' '& [Left Tag axle Outer] & ' '& [Right Rear Outer] & ' '& [Right Rear Inner] & ' '& [Left Rear Inner] & ' '& [Left Rear Outer] & ' '& [Right Rear Rear Outer] & ' '& [Right Rear Rear Inner] & ' '& [Left Rear Rear Inner] & ' '& [Left Rear Rear Outer] & ' ' Like '*2327*'
Thanks again.

View 5 Replies View Related

Modules & VBA :: Quick Way To Export ALL VBA Code To A Text File / Word Document?

Aug 4, 2014

Is there a quick way to export ALL VBA code to a text file/word document?

View 5 Replies View Related







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