Visual Basic :: Selecting Time Field In Query

[access Vba]Problem With An Insert Query In A Memo Field: Run-time Error 3075
Hi,
I've a problem with a simple insert query when the number of characters inserted into a memo field is hight.
this is my code:

Code:
...
Dim fso As New FileSystemObject, ts As TextStream
Dim s As String, sql As String
Dim db as Database
Set db as CurrentDb()

Set fso = CreateObject("Scripting.FileSystemObject")

For i = 1 To selFiles

Set ts = fso.OpenTextFile(.SelectedItems.Item(i), ForReading)

s = ts.ReadAll

ts.Close

sql = "INSERT INTO documenti (lid, dtitle, dtext, ddata) VALUES (" & _
Me.lang.Column(0) & ", '" & _
Replace(nof(.SelectedItems.Item(i)), "'", "''") & "', '" & _
Replace(s, "'", "''") & "', '" & _
Now() & "')"

db.Execute (sql)

Next i
I read a text file and I insert its contents into my database, but when the length of variable 's' is more than 18/20,000 chr I got runtime error 3075, but a memo field could contain up to 65,000 chr ca.
Anyone could suggest me where is the problem?

(PLz Its Urgent, And Its Too Long) Data Field <field Name> Not Found
I am generating a report using shape statements and its hirarichy is like htis
Wing
    class in that wing
        section of a class
            name of student in this section

here is the shape statement

qry = "SHAPE "
     qry = qry & "{ "
     qry = qry & "select wing_name, wing_id from wing "
     qry = qry & "where wing_id = '" & vwing & "'"
     qry = qry & "} "
     qry = qry & "AS com_student_list APPEND "
     qry = qry & "( "
     qry = qry & "( "
     qry = qry & "SHAPE "
     qry = qry & "{ "
     qry = qry & "select class.* from class "
     qry = qry & "} "
     qry = qry & "AS chcom_student_list APPEND "
     qry = qry & "( "
     qry = qry & "( "
     qry = qry & "SHAPE "
     qry = qry & "{ "
     qry = qry & "select section_id,class_id "
     qry = qry & "from class_section "
     qry = qry & "where year = '" & vyear & "' "
     qry = qry & "} "
     qry = qry & "AS ch_chcom_student_list APPEND "
     qry = qry & "( "
     qry = qry & "{ "
     qry = qry & "select a.student_id , "
     qry = qry & "r.fname//' '//r.mname//' '//r.lname as name , "
     qry = qry & "a.section_id "
     qry = qry & "from assign_section a , registration r, student s "
     qry = qry & "Where a.student_id = s.student_id "
     qry = qry & "and s.registration_id = r.registration_id "
     qry = qry & "and a.year = '" & vyear & "' "
     qry = qry & "order by student_id "
     qry = qry & "} "
     qry = qry & "AS ch_ch_chcom_student_list RELATE "
     qry = qry & "'SECTION_ID' TO 'SECTION_ID' "
     qry = qry & ") "
     qry = qry & "AS ch_ch_chcom_student_list "
     qry = qry & ") "
     qry = qry & "AS ch_chcom_student_list RELATE "
     qry = qry & "'CLASS_ID' TO 'CLASS_ID' "
     qry = qry & ") "
     qry = qry & "AS ch_chcom_student_list "
     qry = qry & ") "
     qry = qry & "AS chcom_student_list RELATE "
     qry = qry & "'WING_ID' TO 'WING_ID' "
     qry = qry & ") "
     qry = qry & "AS chcom_student_list "

format of report is like this
Wing ID    SE
Wing name
    class ID SE06
    class
        Section Id SE06BLU
            Studetn Id        Name
            100099        Sabeen Iqbal
            100099        Sabeen Iqbal
            100099        Sabeen Iqbal

first it give this error on class_id (if I delete this txt box )
then gives error for class (if I delete this txt box too )
then gives error for section_id (if I delete this txt box also)
then reports is generated, though the result is correct but I can't get info that these student is are in whcih section of which class

here are the tables
wing
wing id    pk
wing name

class
wing id    fk
class id     pk
class

class_section
class id    fk
sectionId    pk
year pk (composite pk)

assign_section
studentId     fk
section_id    fk
year     fk (composite fK)

I've checked the spelling in the data feild property thsee are correct and
data report event is initialized (i've checked it )



Edited by - haahoou on 2/8/2004 5:10:53 AM

Concatenating Field Values Into One Field For An Update And Insert Query
Hello,

I have an ADO based DB viewer and I have a field where I need to concatenate values from 2 other fields in order to get teh right value. Now the concatenating part is easy, however I need to save this in my DB and and that is where the problem is.
I can concatenate the 2 fields and it shows in the textbox (txtFruit = apple, txtVeggie = corn, txt combo = txtFruit & txtVeggie = applecorn), but when I save it or update it, well it never saves. How would I go about doing this, thanks

Code:
"UPDATE Table SET Fruit = '" & DB_Table.txtFruit.Text & "', Veggie = '" & DB_Table.txtVeggie.Text & "', Combo = '" & DB_Table.txtfruit.Text & DB_Table.txtVeggie.Text & "'"

Edited by - jjoseph on 6/4/2007 10:18:40 PM

Dinstinct Values For One Field Of A Multiple Field Query?
Is it possible to structure an SQL query in such a manner that distinct values for one field are retrieved along with data from other fields of a table, or can this only be done with multiple queries?

Zybron

Selecting From A Field In A Database
As I am a bit of a novice to VB I need help with the following problem. I have a form to add new members to my membership table in a database. The form contains text boxes that write to the table via a command button. One of the text boxes is for 'Membership Number'. I want this text box to automatically come up with the next available number when I open the form. I'm sure that this is a fairly simple question, but I am new to the subject!

ADO, Selecting Field Names Dynamically
I'm trying to pull data out of a Table, but I need it to be variable on the selection of the field names. I've seen it done in DAO, but I need it done in ADO.

This isn't right.. but to give a example
VaryingString = "Field1"
adoRS_TableName!VaryingString = "hello "
VaryingString = "Field2"
adoRS_TableName!VaryingString = "there"
msgbox adoRS_TableName!Field1 & adoRS_TableName!Field2 'Message would be "hello there"

Thanks for the help.

Selecting On Base Of Merge Field.
Hi,

I've got a specific problem, maybe someone can help me...

I'm using BaanIV to make an ASCII .txt delimited mergefile. From BaanIV I also startup a blank document. From this document I want to generate a specific letter.
Which letter depends on some values of my .txt file-fields. For example: when my ASCII textfield "Letter" equals "firstletter"; Word document "Letter1.doc" has to be opend, when "Letter" equals "secondletter", Word document "Letter2.doc" has to be opend, etc. From this opened .doc I preform an automatic mailmerge with my ASCII file.
Finally here's the question: Within Word (in the VBE) I want to program a function in a Macro to check the value of the fileld from the .txt file, how can I do that ??

Regards,

Peter.

SELECTing Records From An Auto Int Field...
I have a field in my file that is type: int & identity field (not for replication).

When I try to run an SQL statement against the file to retreive a record that I know exists it hits EOF and does not return my record. I am converting a string "txtVendID.text" from a text field to do the search into an int by using the cint() function. However it still does not find a match. If I change the key field back to nVarChar everything works but I can't have an auto-incrementing key.

Here's how my SQL statement looks: (searching in an SQL 2000 database)

rs.Open "SELECT * FROM Vendors WHERE Vendor='" & CInt(txtVendID.Text) & "'", con, adOpenForwardOnly, adLockOptimistic

Can anyone see what I am doing wrong ???

User Selecting Field From A File
I am trying to figure out how to allow a user to choose the file that is to be used, THEN choose which field in the file that is to be used.

I have successfully used the Drive, Directory, File List boxes and the user chooses the file. But, now my user wants to be able to see the fields (OR AT LEAST THE NAMES OF THE FIELDS) and then choose which one he wants to manipulate further.

What do I do, ANYONE???
THANKS.

Selecting Field Info From A DB Via Combobox
Hi,
So I'm trying to make a register-type programs that allows the selection of menu items from combo boxes. After a user changes a combo box selection I need the price for the newly selcted item to be displayed in a textbox. I have been trying to get this function to work by connecting to my DB through code (M$ Acces 97, jet 3.51, using ADO) but it is not working. Now I've been trying to select menu records by using a ADODC and linking the relevant textboxes to the ADO DC in order to see if the code Im using is even finding any menu item records...it isn't. I'm kinda at the end of my rope here, I've been trying anything and everything to try and get this working, can someone please point me in the right direction?

Code:Private Sub Combo1_Change()

With mcnConn
        .ConnectionTimeout = 25
        .Provider = "Microsoft.Jet.OLEDB.3.51" ' *** ACCESS
        .Properties("Data Source").Value = "C:Program FilesMicrosoft Visual StudioVB98Giacone.mdb"
        .Open
End With
Dim strSQL1, strSQL2, strSQL3, strSQL4 As String
Dim selection As String
Dim rs7 As New ADODB.Recordset
selection = Combo1.Value

'strSQL1 = "SELECT * FROM PizzaMenu WHERE Pizza LIKE '" & combobox1.Text & "%' "
'rs7.Open strSQL1, mcnConn, adOpenDynamic, adLockPessimistic, adCmdText

Adodc4.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:Program FilesMicrosoft Visual StudioVB98Giacone.mdb"
Adodc4.RecordSource = "SELECT Price FROM PizzaMenu WHERE Pizza =" & "'" & Combo1.Value & "'"
Adodc4.CommandType = adCmdText
Adodc4.CursorType = adOpenKeyset
Adodc4.LockType = adLockOptimistic
Adodc4.Refresh

    If rs7.State = adStateOpen Then
        rs7.Close 'Close the recordset
    End If
Set rs7 = Nothing

    ' **** Close Connection to DB if open ****
    If mcnConn.State = adStateOpen Then
        mcnConn.Close
    End If
    ' **** Clean connection object from memory ****
    Set mcnConn = Nothing

End Sub

Selecting Datagrid (Urgent)
Hi, I need help with some datagrid problems. I'm trying to have my application be able to allow the user to select 1 record on the datagrid and have all the data of that record displayed in a bunch of textboxes. I checked MSDN library and found that "SelBookmarks" sounds pretty similar to what I need. However, I do not quite understand how i can use it. I would appreciate any help. Thanx

Selecting A Field Based In Recordset Index
Say I have the following recordset:

ProfileName     RuleDesc      ProductName  (These are field names)

Soda            Open           Coke
Beer            Restricted     Corona

How could I reference the RuleDesc field for the record with soda as the profileName?

SQL Access - Selecting A Blank Date Field
IF a datefield can be blank, how do I select it?

Fields in job_scheduling
job_date = DATE
job_number = NUMBER Primary Key
job_time = DATE with format (hh:mm AM)

This works fine:

"INSERT INTO job_scheduling (job_number, job_date ) values (3,'11/16/2003');"

So now there is nothing for job_time on that day

SELECT *
FROM job_scheduling
WHERE (job_scheduling.job_number=3 AND job_scheduling.job_date=#11/16/2003# AND job_scheduling.start_time = ??? );

how do I select for job_time = Nothing? I tried 'Null' but it didn't work.

thanks,

Ram09

URGENT - Selecting A Table To Print
Hello, how can I let the user choose a DB table to print, from my dataenvironment¿

F. T. W.

SQL Query For Selecting First/Middle/Last Name
Anybody knows how to write a sql query to concatenate the first/middle/last name to get the full name without extra spaces in between in single query??? I tried all the ways but i am getting extra space in between..

Select [First Name] + IsNull([Middle Name]),'') + ' ' + IsNull([Last Name]),'') From [Members]

remember middle/last name can be empty or NULL

Selecting From Query Results
Hi all, I have a query which returns some results:

Server    Last    First    Email
------------------------------------------------------------
ABC3    Cosby    Bill    CosbyB@aol.com
XYZ    Armstrong    Lance    ArmstrongJ@aol.com
XYZ1    Brady    Wayne    Bradyw@aaol.com
ABC    Hatcher    Terri    HatcherT@aolcom
ABC    Dorian    Jimmy    doctorj@aol.com
ABC2    Brown    Carrie    JohnsonC@aol.com
XYZ    Dorian    Jimmy    doctorj@aol.com
XYZ2    Burns    Nick    BurnsN@aol.com

I am trying to run another query,

Code:SELECT ServerQuery.Email
FROM ServerQuery
WHERE ServeQuery.Server = "ABC";

which should result in the email addresses of terri hatcher and jimmy dorian. I am having trouble, is what im doing even valid? Is there another way around/do I have to store these results in a table to manipulate them?

Thanks in advance.

-Doctor

Edited by - doctor on 2/9/2005 1:14:28 PM

Selecting Values In A DataCombo Linked To A Calculated Field In The Recordset
Hi all,

Please help me with this one:

When I try to choose an entry in a DataCombo I get the following message: "Field not updatable, Bound Property Name: boundText, FieldName: UserName"

These are the commands that fill the DataCombo:

strcriteria = "SELECT Officers_Functional.Worker_Number, [General_Users].[First_Name] & chr$(32) & [General_Users].[Middle_Name] & chr$(32) & [General_Users].[Family_Name] AS UserName FROM General_Users INNER JOIN Officers_Functional ON General_Users.Worker_Number = Officers_Functional.Worker_Number WHERE (((Officers_Functional.Ordinal_Projects)=" & iProject & "))"

rsUsers.Open strcriteria, adoConn, 1, 3

With DCmbCurrentOwner
Set .DataSource = rsUsers
Set .RowSource = rsUsers
.ListField = "UserName"
.DataField = "UserName"
.BoundColumn = "Worker_Number"
.BoundText = ""
End With

As you see, the field UserName is a "calculated" field.
Other DataCombos in the program that are filled without "calculated" fields, work fine.

What is the reason for this annoying message and how can it be avoided?

Selecting Values In A DataCombo Connected To A Calculated Field In The Recordset
Hi all,

Please help me with this one:

When I try to choose an entry in a DataCombo I get the following message: "Field not updatable, Bound Property Name: boundText, FieldName: UserName"

These are the commands that fill the DataCombo:
strcriteria = "SELECT Officers_Functional.Worker_Number, [General_Users].[First_Name] & chr$(32) & [General_Users].[Middle_Name] & chr$(32) & [General_Users].[Family_Name] AS UserName FROM General_Users INNER JOIN Officers_Functional ON General_Users.Worker_Number = Officers_Functional.Worker_Number WHERE (((Officers_Functional.Ordinal_Projects)=" & iProject & "))"
rsUsers.Open strcriteria, adoConn, 1, 3
With DCmbCurrentOwner
Set .DataSource = rsUsers
Set .RowSource = rsUsers
.ListField = "UserName"
.DataField = "UserName"
.BoundColumn = "Worker_Number"
.BoundText = ""
End With

As you see, the field UserName is a "calculated" field.
Other DataCombos in the program that are filled without "calculated" fields, work fine.

What is the reason for this annoying message and how can it be avoided?

MSACCESS2000-Selecting Sample Query
Could someone please help me with some code that would allow me to do the following in MS Access 2000.
i have 5 months with 2 days in each month each having 20 transactions for a total of 200 transactions and each transaction has a random # assigned to it.

i want a sample of 30 transactions with the highest random number, and evenly divided amongst the days (i.e 3 transactions for each day with the highest random number).

I have needed this several times and so i hope someone can help.

Crystal Reports && Dynamic Query (even Tables And Fields Changes In The Query At Run Time)

Hi there!

I need to link the crystal report to a query that is generated dynamically. I will be knowing about which table to be linked and which fields to be retrieved at run time only. Fields are refered using an alias in all the query output to get a common name for all. I cant use a dataset or TFX file solution as mentioned earlier as i am not aware of the tables and fiels till runtime. Else i have to hardcode all the possible combinations in different tft file and link .

i tried with a temporarytable but that too doesnt seems to work.

Can anyone guide me of any simpler methode of doing this?

Bipin

Selecting Records From Query Where Sum Of Values Is A Certain Amount
I am trying to manipulate a query whereby if I sort the query in descending date it will select the most recent orders which will add up to a particular value (and include the first record which will take the total over that value)

i.e. if my chosen value is 100, there are orders in date order of 40, 30, 10, 10, 15, 40, 35, 50...

it will select the first 5 records (40+30+10+10+15=105), but no others. Even if it could select the first 4 (sum is less than 100) this would be a help.

I'm figuring out how to do it in VBA/SQL by going through records one by one and adding up the values, then selecting the appropriate records and moving them into a table. However, I was just wondering if there is a trick something along the lines of 'top10' which would save time and reduce potential bugginess, or even a one or two line command which may do it.

Advanced thanks to any star who can help.

Gray

Selecting Range Of Dates Using Access Query
I have tryed using following query to select range of dates:

SELECT * FROM DataBase WHERE Date >= StartDate AND Date <= EndDate

However this is not working
Any ideas?
10x in advance!

I Need Help With Selecting And Updating The Database After Running A Query.
I have a form that displays all the student names as a listbox, I also have a View Student Details button in the form. When
I click the button, it runs a query and displays a new form "frmstudent details" of the selected student from the listbox of the previous form

In the student details form I have an combo box that displays the school name of the student. How can I select and update the school name from the combo box. At the moment I have the studentdetails form's record-set set to a query that displays the current student name and current school name.

The Table structure I have is

tblStudent: studentID, fkschoolID, studentName
tblSchool: schoolID, schoolName

Here is my VBA code
Code:
Private Sub cmdViewStudent_Click()
    If IsNull(Me.lstStudent.Column(0)) Then
        MsgBox "You must select a Stuent Name from the list to complete this operation", vbOKOnly, "Pilot Error!!"
    Else
        ShowStudentDetails ("Dbl")
    End If
End Sub

Sub ShowStudentDetails Function is:
Code:
Sub ShowStudentDetails(Optional WClick As String)

        Dim lngID As Long
        Dim strSQL As String
        Dim strSELECT As String
        Dim strFROM As String
        Dim strWHERE As String

        lngStudent = Me.lstStudent.Column(0)

        strSELECT = "SELECT tblStudent.studentID, tblStudent.fullName, tblSchool.Name "

        strFROM = "FROM tblSchool INNER JOIN tblDesigner ON tblSchool.schoolID=tblStudent.fkschoolID"

        If WClick = "Dbl" Then
            strWHERE = strWHERE & "tblStudent.studentID = " & lngStudent
        End If

        strSQL = strSELECT & strFROM
        If strWHERE <> " " Then strSQL = strSQL & " WHERE " & strWHERE

        CurrentDb.QueryDefs("qryStudentForm").SQL = strSQL

        DoCmd.OpenForm "frmMainForm"

End Sub

Thanks,

Multiple Socket Udp Query And Ping From Senddata Time To Dataarryval Time
again a question

I am using a udp socket to query a bunch of game servers ,that part is ok but i cant get the server ping unless i use the ping api,that makes it go alot slower because i have to wait for one server ping in orther to get the next, i was wondering if someone know the secret of how to query the servers and get the ping from the time i send the data to the time the data arrives, like the professional server query tools do (All seeing eye ,xfire,and others).ive managed to get part of that working using gettickcount from the time i send to the time it arryves but still it doesnt work properly...

Time Elapsed In SQL Query In VB Vrs SQL Query In Access
Hi guys,

Should it be normal for a SQL query in VB to take longer than in Access. Both queries are out to a SQL server and it is taking 30 seconds for MS Access and almost 4 minutes for VB. Is there anything I am missing or are there different engines in each of the SQL queries?

Thanks a lot,

- George

SIMPLE QUESTION But Im Thick! - Selecting Individual Record To Run An Update Query On
When I edit a field on a record I want a query to run on that record only - how can I do this simply?

For instance I have a table called "Pot_Cust_Table" that has a primary key which is a text field("PC_ID"). The way I am doing the update query is running it on ALL records but the time it takes will keep increasing with the more records I do.

Basically I want to just choose the one record to run the query on but I dont know how to do this?

Please can any one help?

Thanks

A Combo Box For Selecting Time ... Does This Exist?
I am allowing the users to select their start and finish times for their business for each day of the week. If I manually use a combo box and plug in the values with say a 5 minute interval, thats a BIG combo box.

Are there any components that work similiar to a DTPicker? ... but for time?

Selecting Only One Row At A Time In Flexgrid While Holding Leftmousebutton
Hello,

I have a problem:

I have a MSFlexgrid filled with data, selectionmode = 1 (by row). There is a selec

Now, i want to drag a row to another control, but when doing this...the other rows that move on with my mouse get selected also.

In other words..how can I limit the selection mode in MSFlexgrid to only ONE row att the time?

Parameter Field! Urgent!!!!
Hey guys? the doubt is:

I try to pass a data value by parameter field, and i use this line of command(IN VB):

RptFat.ParameterFields(0) = "DataInicio;" & Left(dtpPeriodoDe.Value, 10) & ";True"

when i run the system in visual basic, in the moment are data is putted on report, shows this error msg:
"err 20533 Invalid parameter field value"
the parameter field is configured for data values. i haved configured to string values,but, i continue without solutions to this trouble. People, help me!
Thanks a lot!

Urgent- Returing Value Of DB Field??
I write the following function to return the value of the field of database.

I'm returning the value like:
GetDataValue = ars!FieldName 'Error
But this line gives mee the error.

some 1 plz help me how can i return the value of database field??

public Function GetDataValue(byval FieldName as string, byval DistrictName as string)

Dim cnn as ADODB.Connection
Dim ars as ADODB.Recordset

set cnn = new ADODB.Connection
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:MapData.mdb;Persist Security Info=false"
.Open
End With

SQL = "select " + FieldName + " from DistData where District = " + "'" + DistrictName + "'"
set ars = cnn.Execute(SQL)

GetDataValue = ars!FieldName 'error
ars.Close
cnn.Close

set ars = nothing
set cnn = nothing
End Function

...---.....
Hassan Zia
Hassan_Zia@mathtech-pk.com

Getting A Certain Field From Query
i would like to get a data from a QUERY and display its "gotten" data to a datagride, but an error always appears when i run the program... it says [ADODC]: unknown error [ado].... here is a sample code...

Code:
If cboCategory.Text = "First Name" Then
SQL = "SELECT LogInLogoutTable.LogDate, Employees.FirstName, Employees.MiddleName, Employees.LastName, Employees.Position, LogInLogoutTable.Late, LogInLogoutTable.Overtime, LogInLogoutTable.Absent, LogInLogoutTable.NumberOfLate, LogInLogoutTable.NumberOfOvertime, LogInLogoutTable.NumberOfAbsent FROM Employees, LogInLogoutTable Where [Employees.FirstName] like '%" + txtSearch.Text + "%'"
End If

AdoEmployeeStatusReport.RecordSource = SQL

AdoEmployeeStatusReport.Refresh
i think there is still an error in my codes somewhere in the bold area, please correct my mistakes....

Dbgrid With Memo Field, URGENT
I have a dbgrid field which displays a memo field from Access. It was working fine in Access97, but after the upgrade to Access 2000, I can't seem to limit the width of the display column and make the memo text wrap around. The text simply keeps going and one is required to put the cursor in the field and arrow right to see the rest of the text. Does anyone know what I need to do to fix this? Thanks in advance.

Memo Field Returning As Null? URGENT
I have a Word VBA app that requires a large text entry (large paragraph) to be stored for future use. The text stores ok, however, when I pull the data, it returns a null value and I have no data. I changed the data type in Access to Text and that fixed the problem, but the limitations of the text data type aren't acceptable in my scenario.

Code:
Set objRec = objDB.Execute("SELECT LetterData.TextEntered FROM LetterData WHERE LetterData.Case='" & strCase & "'")

If objRec.BOF = True Or objRec.EOF = True Then
MsgBox "No information found for case number " & strCase & ".", vbOKOnly
+ vbCritical, "Error"
Set objRec = Nothing
objDB.Close
ElseIf objRec.RecordCount <> 0 Then
'Use previously entered text from user, if nothing in database, do nothing
If objRec("TextEntered") <> Null Then
UserText$ = objRec("TextEntered").GetChunk(65000)
Else
UserText$ = "Null set returned."
End If
objDB.Close
ElseIf objRec.RecordCount = Null Then
MsgBox "No information found for case number " & strCase & ".", vbOKOnly
+ vbInformation, "Empty"
Set objRec = Nothing
objDB.Close
End If

Ideas anyone?

URGENT: Crystal Reports And BLOB Field.
Hi guys,

its little urgent!

i wish to embedd a blob field which stores a picture in my crystal report in detail-tab. somehow its not been displayed... kindly help me for this problem.

well, all i have done is ... set up the connection for the report, and put the picture field on the report in detail tab.

plz helpppppppppppppppppppppppp

butterfly.

Daring ideas are like chessmen moved forward. They may be beaten, but they may start a winning game.

Vb-sql Server Date Field Problem:URGENT
in my vb application i am using DTPicker in forms.
when i am trying to insert datefield as dd/mm/yy format in my table
(sql server) through my vb application error is occured . the error is

error -2147217913 conversion of a char data type to a datetime data
type resulted in an out of datetime value

when i try to insert datefield on dd/mm/yyyy through query analyser there is
no problem

help me and send some soucecode to solve this problem in my vb application
its very urgent

Query Field Reference
I am wanting to set a defined variable to a field within a query. Here is the following:
********************************
Dim qryLoc1Total As QueryDef
Dim strTotLoc1 As String

Set qryLoc1Total = CurrentDb.QueryDefs _("qry_SalesTotalCurMonth_TotalsLoc1")
*********************************

How do I make strTotloc1 variable equal a field called TotalSales within qryLoc1Total?

Thanks

Query Field On/Off Control?
In the Query design mode there is row titled "Show" with check boxes for each Query field.

Is there any way to check/uncheck that box through VB code?

One Field Is Missing From Query, WHY??
Here is my SQL statement to display all the query in the mshflexgrid:

VB Code:
SELECT Cutt_Master.Month, Cutt_Master.Prod_Code, Cutt_Master.Part_Code,Cutt_Master.Cutt_Date,((Cutt_Master.Qty/1000)*Cost_Price.Origin_Cost) AS Amount, Cost_Price.Origin_Cost,Cost_Price.Insp_Cost, Cost_Price.InProcess_Cost, Cost_Price.Sales_Price " & _ "FROM Cutt_Master INNER JOIN Cost_Price ON Cutt_Master.Part_Code = Cost_Price.Part_Code " & _ "WHERE Cutt_Date BETWEEN #" & dtpFrom.Value & "# AND #" & dtpUntil.Value & "#

but only cutt_date is missing in the mshflexgrid...
i dont know why...
anyone knows??

Memo Field In Query
Guys

I have something a tad strange happening but I'm sure one of you clever chaps will be able to figure it out. Ok, here goes.

This query:

VB Code:
SELECT tbljobdetails.jobno, tbljobdetails.clientID, tbljobdetails.cpc1, tbljobdetails.cpc2,tbljobdetails.cpc3, tbljobdetails.cpc4, tbljobdetails.prodname1, tbljobdetails.prodname2,tbljobdetails.prodname3, tbljobdetails.prodname4, tbljobdetails.plasno1, tbljobdetails.plasno2,tbljobdetails.plasno3, tbljobdetails.plasno4, tbljobdetails.batchesno1, tbljobdetails.batchesno2,tbljobdetails.batchesno3, tbljobdetails.batchesno4, '5137' AS clientid, 'blah blah'AS customer, 'L:mydatalahlah.dat' AS Infile,[b]tblerrortext.errortext AS Errortext[/b],Sum(([tbljobdetails].[plasno1]+[tbljobdetails].[plasno2]+[tbljobdetails].[plasno3]+[tbljobdetails].[plasno4]))AS sumPlas, sum(([tbljobdetails].[batchesno1]+[tbljobdetails].[batchesno2]+[tbljobdetails].[batchesno3]+[tbljobdetails].[batchesno4]))as sumBatches FROM tbljobdetails LEFT JOIN tblerrortext ON tbljobdetails.jobno = tblerrortext.JobNoGROUP BY tbljobdetails.jobno, tbljobdetails.clientID, tbljobdetails.cpc1, tbljobdetails.cpc2, tbljobdetails.cpc3,tbljobdetails.cpc4, tbljobdetails.prodname1, tbljobdetails.prodname2, tbljobdetails.prodname3, tbljobdetails.prodname4,tbljobdetails.plasno1, tbljobdetails.plasno2, tbljobdetails.plasno3, tbljobdetails.plasno4, tbljobdetails.batchesno1,tbljobdetails.batchesno2, tbljobdetails.batchesno3, tbljobdetails.batchesno4, '1234','blah blah','L:lah1234567lah.dat', tblerrortext.errortext, tbljobdetails.id HAVING (((tbljobdetails.id)=102));"

Gives me the information that I need but the tblerrortext.errortext returns a '?' in the query but the actual value in the tblerrortext table is 'Here is an error' or something.

Any ideas!?

Cheers guys

Query Field Error
I am using

myquery = "select * from act where Phone A = '2144331'"

to query my data base. The field name is 'Phone A'. It is a Text field. It seams that my field name is not exepted because it is made out of two words Phone & A. How can I fix this problem without renaming the field.

Thanks

Sanitago

Query An Encrypted Field
I inherited this system that encrypts data in a field that I need to query (In Access now) yet I cannot figure out how to query this field based upon the encryptions. For Example I need to query this where this field = "1". IN the DB this is how the field is encrypted from Table DCRLogFast on Field Books:

IINNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNINNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN NNNNNNNNNNNNNNNNNNINNNNINNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''
'Now in the code in VB this is how the table is queried and 'it works! It replaces the position of the field with an I for the number entered. The 'temp' variable is the user input, whereas here I would have entered "1".

Books = "??????????????????????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????????????????????????????????????????????????????????? ?"

Mid$(Books, temp, 1) = "I"

SQL = "Select * from DcrLogFast where Books like '" & Books & "'"

Anybody got any query suggestions for Access.

VB6 Query On A Date Field
I need assistance concatenating the # character to a variable that contains a date in an sql statement.

The portion of the statement in qestions should look something like this.

#variablecontaingdate#

Any help woulod be appreciated.

Query XML Data From DB Field
Hi All,

I have an XML Doc loaded into a SQL Server DB field of type 'text'. I want to query the XML info, here is the SQL Server code I have so far:

Code:
DECLARE @DOC varchar (8000)
DECLARE @idoc int

select @doc = xmldata from xmltest
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/NeededInfo')
WITH (NeededInfo ntext '@mp:xmltext')

This is OK but the XML field is of type text beacuse it's HUGE and I can't declare @DOC as type text. So, How do I get the entire XML info into the 'sp_xml_preparedocument @idoc OUTPUT, @doc' stored proc?

THX

Cyphin
~Of all the things I've lost, I miss my mind the most.~
~Iz vseh veshei chto ya poteryal, ya bol'she vsego skuchayu po rassudku.~

Edited by - Cyphin78 on 7/7/2005 1:40:10 PM

URGENT : Format Of Password Field In Access Database
Hi all,

i have a problem in vb6.i m using access database as backend and storeing the new user in the table. table name is Set_pass.if i m store password "vijay" then it store in database as it is like vijay.But i want to store password in to access database any other format like * or binery formet or any unread format. I hope all of u guys understend my problem.plzzzz help me.

i want to store password into access database like
¯”Ê™¿³~ùdW§'R›ÓnžA®ùð
**********
or any other format

plzzzzzzzzz provied me solution which decode the value from database and encode the value from text box

thankx in advance

MS Access VBA: SQL Query -> ADD Autonumber Field
Hi,
i'm new to coding in VBA and Microsoft Access, so the problem probably is easy to solve

I do the following query and get the table you see underneath:
Hi,

ich mach mittels folgendem SQL Befehl eine query in meinem visual Basic zusatz Programm zu Microsoft acces:

Code:
strSQL = "SELECT ModifiedTables.ModifiedTableID, Tabelle1.Act_Author, Tabelle1.ModificationNumber, ModificationLog.Type " & _
"INTO UserInteraktionen" & i & " " & _
"FROM ModifiedTables RIGHT JOIN (ModificationLog RIGHT JOIN Tabelle1 ON ModificationLog.ModificationNumber = Tabelle1.ModificationNumber) ON ModifiedTables.ModifiedTableID = ModificationLog.ModifiedTableID " & _
"WHERE (((Tabelle1.Act_Author)=[Benutzername]));"
http://home.pages.at/gxesch/UserInteraktionen1.Tab.jpg

my problem is:
the ModifiedTableID field is on "autowert" (="autonumber" in english). Does this mean that MS Access uses this field as Primary Key?
I need a new field which adds autonumbers to each record and it can be used as primary key.
When i try to add such a field, i get an error telling me, there already is a "autonumber" (autowert) field.

How can i solve this?
thanks a lot in advance!

PS: Sorry for my not perfect enlish ;-)

Access 2000 Run Query For Nothing In The Field?
Is it possible to run a query and bring up all the records that have nothing in that field. I tried using this: = "" but it still doesnt bring up those records that have that field blank.

Thanks Nick

How Define An Excel Field In A SQL Query
Hi,
I need to realize a conditional insert from Excel to SQL.
The code is the following:

INSERT INTO MKT_TST.dbo.TEMP
(conto,importo,versione,anno,mese,rettifica)
SELECT * FROM OPENQUERY(BASE_DATI,'SELECT * FROM [BASE_DATI$]')
WHERE TEMP.versione <> (SELECT * FROM OPENQUERY(BASE_DATI,'SELECT * FROM [BASE_DATI$.Versione]'))

I have an error related to field VERSIONE;
the message error is:
OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'BASE_DATI$.Versione'

What's the correct way to call the Excel field?

Thank u!

Selecting Time Field In Query - Urgent