Search results

Hi all, A db shows its records in a subform from where I click a field to edit the contents of that field in a pop-up form (see pic). All works well except when the field contains no data. The pop-up opens blank (because there is nothing in that field) but will not update the field with typed...

Excellent Guus2005, that did it. I'm forever in debt. :)

I have table1 with fields 'project', field1, field2, field3 and table2 with fields 'project', fieldA, fieldB, fieldC. I need a query that has all fields from table1 and only fieldA from table2. What kind of join property should it have in order for me NOT to have duplicates? 'project' field in...

Well, databases are evolving and the one I'm currently working on was designed eons ago without much in mind. As for the Left function, I was afraid it had to come to that just because I wanted a quick simple way without much code involve. Thanks both for your inputs. Much appreciated.

Dmax and Max work on the Ticket field in the linked table, but again, that is a text field and I need to strip the alpha characters from the string. Unless there is a work around it?

I have tried several approaches but still no luck. Could the problem be coming from the Val() function? Should I inspect my table design or something? Any other suggestions?

It works only if my table is not a linked table otherwise it fails with a type mismatched error.

See the 'Ticket' field is a text field so dmax returns '999' as the highest value which is not true. In order for me to strip the 'a' and 'b' from the string I'm using Val() function.

"Run-time error '62506': Data type mismatch in criteria expression." This occurs only when the "tblHolds" table is linked. NextNum = Val(DMax("Ticket", "tblHolds")) does not work as I'm trying to get the numbers from a string tho it does not generate the error as mentioned above.

Anyone has encountered a problem with Dmax and linked tables? Or is it my code? The following line generates a run-time error '62506' only when my table is linked but not when the table resides in the db. Any ideas as to why? NextNum = DMax("Val(Ticket)", "tblHolds") The 'Ticket' field is a...

I'm no expert here, but by looking at your code, are you creating a txt file? If not, what do you call your excel object? I don't see it in your code. Give the following a try: 'dim your variables Dim ExcelApp As Object Dim rst As Recordset 'create your excel object Set ExcelApp =...

I don't see your cell reference unless "#1" is it(?) but you have to reference the cell you want your data to go to, something like If (Not (BlankField(.Fields("FIRST NAME").Value))) Then .Range("A1") = Format(.Fields("FIRST NAME").Value Else .Range("A1") = "" End If When you have to...

I solved it with this: "INSERT INTO tblMstrRollInfo( LMPReference, MasterRoll, Set_, Position )" & _ "SELECT RefNo, Field9, Field10, Field11 " & _ "FROM tblTemp " & _ "LEFT JOIN tblMstrRollInfo ON tblTemp.Field9 = tblMstrRollInfo.MasterRoll " & _ "WHERE (((tblTemp.Field9) Not In...

FoFa, thanks for your reply. I tested your code but it does not filter duplicate records. I tried modifying it with other tables but did not work. Do you mind explaining a bit the "T left join" line? Thanks, George

Hello all, I am moving records from one temp table to my main table using a SQL INSERT INTO statement. I got that part right. What I need help on is creating the criteria aspect of it. The query should look into the values of the main table and not import the record if it will be duplicated...

Any body on this?

I need help with creating a SQL statement that also displays null values. I have a form with list boxes. The code below creates the SQL statement by reading the values from the lists: For Each varItem In Me.lstMachine.ItemsSelected strMachine = strMachine & ",'" &...

You have no idea how many times I proof read this thing...Thanks. Now off to the eye doctor ;)

Hello All, The following SQL statement is promting the user to enter machine number. Why? If I remove that part of the statement then it works fine. SELECT tblData.* FROM tblData WHERE tlbData.[Machine] IN('WH3') AND tblData.[Project] Like '*' AND tblData.[Repeat] Like '*' AND...

Hello All, I am trying to build a Select Case rutine where something happens if the time field is betweeb a given time slot. This does not seem to work: Dim varTime As String varTime = Format(Now, "h:mm:ss AMPM") Select Case varTime Case Is > #11:59:00 PM# And varTime < #8:00:00 AM# MsgBox...